Query Help -- 2

[james777]

I need to collapse rows into one appened columns
Example: Input
col1 col2 col3 col4

1 A YY Oracle
1 A YY Siebel
1 A YY Sysbase

OutPut: Should be in single row as

1 A YY Oracle,Siebel,Sysbase..

There might be 1.. n Col4 values Unknown..

Thanks

 

[SantaMufasa]

James,

Although there are several ways to produce the results you want, this is the way I chose to do it:

Section 1 -- Sample data:

col a heading "Col1" format 9999
col b heading "Col2" format a4
col c heading "Col3" format a4
col d heading "Col4" format a10
select col1 a, col2 b, col3 c, col4 d from mytable
/

 Col1 Col2 Col3 Col4
----- ---- ---- ----------
    1 A    YY   Oracle
    1 A    YY   Siebel
    1 A    YY   Sybase
    2 B    ZZ   Oracle
    2 B    ZZ   Siebel
    2 B    ZZ   Sybase
    2 B    ZZ   DB2
    2 B    ZZ   MySQL
    2 B    ZZ   SQL Server
    2 B    ZZ   Informix

10 rows selected.

Section 2 -- The results you requested:

set linesize 200
col e heading "Strung-together Col4 Values" format a60
select col1 a, col2 b, col3 c, batchCol4(col1,col2,col3) e
from mytable
group by col1, col2, col3
/

 Col1 Col2 Col3 Strung-together Col4 Values
----- ---- ---- --------------------------------------------------
    1 A    YY   Oracle,Siebel,Sybase
    2 B    ZZ   Oracle,Siebel,Sybase,DB2,MySQL,SQL Server,Informix

2 rows selected.

Section 3 -- The "BatchCol4" function:

create or replace function batchCol4 (c1 number,c2 varchar2,c3 varchar2)
    return varchar2
is
    hold_string    varchar2(4000);
    separator      varchar2(2);
begin
    for r in (select * from mytable where c1=col1 and c2=col2 and c3=col3) loop
        hold_string := hold_string||separator||r.col4;
        separator := ',';
    end loop;
    return hold_string;
end;
/

Function created.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:02 (09Dec04) UTC (aka "GMT" and "Zulu"),
@ 11:02 (09Dec04) Mountain Time

 

[james777]

Mufasa,

Thank you for your answer. I did the same way using function
and calling it in SQL. But i am trying using ONLY sql.. no pl/sql..

Thank You
Jim

 

[SantaMufasa]

James,

Sounds like you would be a great player of the card game "Carlotta". Have you ever heard of it? That's the one where you make up the rules as you go along . If you want us to happily spend time building answers for your questions, please give us all the rules/restrictions/specifications in the initial post. Don't have us go off spending significant parts of an hour, building a working solution, and then tell us, "I already did that, but I want it in SQL only." Does your boss do this to you when s/he gives you an assignment?

If you would have said, "I already successfully built a solution using a PL/SQL function," I would have saved a twenty minutes and confirmed, "That's how I would have done it...Any SQL-only function would probably be less efficient...but that's only if I could think of a way to do it in SQL, which I can't."

So, good luck. I'll look forward to seeing someone suggest a way to do it in SQL only.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 03:35 (10Dec04) UTC (aka "GMT" and "Zulu"),
@ 20:35 (09Dec04) Mountain Time

 

[carp]

Dave -
You know perfectly well bosses don't do that. That's the USERS' job!

James -
Concur with Dave - I don't think you can do this in the general sense with SQL. If you had a set number of rows per group you could rig up a kluge to do it, but your best bet is to use a function that returns the string to you.

 

[acct98]

I tried Dave function but got the following error:
Warning: Function created withe compilation errors
How can I find the compilation errors?

 

[SantaMufasa]

Acct,

Following the SQL*Plus disclosure, "Warning: Function created withe compilation errors", issue the SQL*Plus command, "show errors".

If you cannot determine the problem from "show errors", post your code and the error listing here, and we'll be glad to help.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 12:34 (28Dec04) UTC (aka "GMT" and "Zulu"),
@ 05:34 (28Dec04) Mountain Time

 

[acct98]

SQL> CREATE OR REPLACE FUNCTION MANYNAMES(SSN VARCHAR2, VENDOR_NAME VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 HOLD_STRING VARCHAR2(4000);
5 SEPARATOR VARCHAR2(2);
6 BEGIN
7 FOR R IN (select ssn, vendor_name
8 from vendors
9 where ssn like '092%'
10 and ventype = 'E'
11 AND SSN = SSN)LOOP
12 HOLD_STRING :=HOLD_STRING||SEPARATOR||MANYNAMES;
13 SEPARATOR :=',';
14 END LOOP;
15 RETURN HOLD_STRING;
16 END;
17 /

Warning: Function created with compilation errors.

SQL> SHOW ERRORS
Errors for FUNCTION MANYNAMES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/9 PL/SQL: Statement ignored
12/47 PLS-00306: wrong number or types of arguments in call to
'MANYNAMES'

 

[SantaMufasa]

Acct,

Replace the line in your code with this one:

HOLD_STRING :=HOLD_STRING||SEPARATOR||[b]r.vendor_name[/b];

The way you have it now, your function recursively calls itself, which is generally a dangerous activity and considered poor form.

Let us know of your success,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 13:31 (28Dec04) UTC (aka "GMT" and "Zulu"),
@ 06:31 (28Dec04) Mountain Time

 

[acct98]

No errors. How do I get the following:

Col1 Col2 Col3 Strung-together Col4 Values
----- ---- ---- --------------------------------------------------
1 A YY Oracle,Siebel,Sybase
2 B ZZ Oracle,Siebel,Sybase,DB2,MySQL,SQL Server,Informix

 

[SantaMufasa]

Acct,

I'm not sure I understand your question. Does my post from "18:02 (09Dec04) UTC" not give you the information you need?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 14:30 (28Dec04) UTC (aka "GMT" and "Zulu"),
@ 07:30 (28Dec04) Mountain Time

 

[acct98]

I created the function [No Errors]. However, no results are displayed.

I set serveroutput on expecting the results to be displayed but nothing.

Do I need to do a DBMS_output.put_line????

 

[acct98]

Dave,

How did you the " batchCol4(col1,col2,col3) e " to work.

 

[SantaMufasa]

Acct,

First, if you follow my example, above, you do not rely upon "SET SERVEROUTPUT ON"...output is directly to the screen from a SELECT statement.

Refer back to my "Section 2", in my first post (@ 18:02 (09Dec04) UTC) above. That is what generates your output and references the user-defined function:

select col1 a, col2 b, col3 c, batchCol4(col1,col2,col3) e
from mytable
group by col1, col2, col3

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 06:28 (30Dec04) UTC (aka "GMT" and "Zulu"),
@ 23:28 (29Dec04) Mountain Time

 

[acct98]

I not sure it the FOR loop will work with what I am trying to accomplish.

I have one table:
Test1
SSN VARCHAR2(9)
VENDOR_NAME VARCHAR2(30)


Is it possible to use you example to create the following?:
SSN VENDOR_NAMES
123456789 DOE, JANE ~ SMITH, JANE ~ THOMAS, JANE

 

[SantaMufasa]

Acct,

You're teasing poor, ol' Santa, right? I'll bet you just like to imagine a fat man jumping through hoops. Okay, well I guess I need the exercise. In your case, the code is even simpler than my original code for James:

create or replace function batchNames (SSN_In varchar2)
    return varchar2
is
    hold_string    varchar2(4000);
    separator      varchar2(3);
begin
    for r in (select * from test1 where SSN = SSN_IN) loop
        hold_string := hold_string||separator||r.vendor_name;
        separator := ' ~ ';
    end loop;
    return hold_string;
end;
/

Function created.

set linesize 200
col e heading "Strung-together Vendor Names" format a60
select ssn, batchNames(ssn) e
from test1
group by ssn
/

SSN       Strung-together Vendor Names
--------- --------------------------------------
123456789 DOE, JANE ~ SMITH, JANE ~ THOMAS, JANE

Let us know if this satisfies your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 18:31 (30Dec04) UTC (aka "GMT" and "Zulu"),
@ 11:31 (30Dec04) Mountain Time

 

[carp]

Sorry Dave, but I just had to do this one last time this year!
Greater efficiency and maintainability is achieved with:

create or replace function batchNames (SSN_In varchar2)
    return varchar2
is
    hold_string    varchar2(4000);
    separator      varchar2(3) := '~';
begin
    for r in (select * from test1 where SSN = SSN_IN) loop
        hold_string := hold_string||separator||r.vendor_name;
    end loop;
    return hold_string;
end;
/

It's been a "challenging" year, and I have to say that being able to work with you (albeit remotely) via T/T has helped immensely! Thank you, my friend, and I look forward to more adventures with you in 2005!

 

[SantaMufasa]

Carp (Dave B.),

You are right...greater efficiency, but different results. Your version places an extraneous "~ " in front of the first name in the string.

And yes, it is great to stay in regular collaborative contact via Tek-Tips.

Best of wishes to you and my other Tek-Tips friends,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:15 (30Dec04) UTC (aka "GMT" and "Zulu"),
@ 16:15 (30Dec04) Mountain Time

 

[carp]

Oh sure - get correct on me!
OK, how about this?!

create or replace function batchNames (SSN_In varchar2)
    return varchar2
is
    hold_string    varchar2(4000);
    separator      varchar2(3) := '~';
begin
    for r in (select * from test1 where SSN = SSN_IN) loop
        hold_string := hold_string||separator||r.vendor_name;
    end loop;
     return ltrim(hold_string,'~');
end;
/

Of course, now the relative efficiency flips if there are only one or two rows! And once again I succeed in muddying relatively clear waters - a fitting close for this year.

And Dave - BOTH of us are to old and convex to be jumping through hoops!

 

[SantaMufasa]

Yep,

I'll buy that version. And I always enjoy the extra insight that comes from your "muddying relatively clear waters."

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:26 (30Dec04) UTC (aka "GMT" and "Zulu"),
@ 16:26 (30Dec04) Mountain Time