We help IT Professionals succeed at work.

How to convert rows to comma separated values

pvsbandi
pvsbandi asked
on
Hi, i'm looking for a SQL code (not a stored procedure or a function) to achieve the following.
 Kindly help.

DATA

 NAME              RELATIVE   NAME_ID    RELATIVE_ID      RELATION
JOSHUA	     Jeffrey	         123             346                  Brother
JOSHUA	    Cheyenne          123             459                   Cousin
JOSHUA	     Aryan               123             548                    Friend


Desired OUTPUT

 NAME                  RELATIVES                 NAME_ID      RELATIVE_ID              RELATION
JOSHUA          Jeffrey,Cheyenne,Aryan         123        346,459,548         Brother,Cousin,Friend

Open in new window

Comment
Watch Question

View Solution Only

DannyOfSweden

Commented:
Hi Pysbandi,
Here's how to do it:

select NAME+',', RELATIVE+',', NAME_ID+',', RELATIVE_ID+',', RELATION
from TABLE1


Add "as Name" etc... after each expression if you need to return correct column-names as well. But I think you are just interested of the data that is returned.  =)

Good luck,
Danny
Kent OlsenData Warehouse / Database Architect

Commented:
Hi pvsbandi,

If you've got the DB2 command line available to you, the EXPORT command is probably the easiest.


Good Luck,
Kent
DannyOfSweden

Commented:
Hi again Pysbandi,
I just realised I gave you SQL Server, if you want to do it in DB2 you probably need to use the CONCAT command:

select CONCAT(NAME,','), CONCAT(RELATIVE,','), CONCAT(NAME_ID,','), CONCAT(RELATIVE_ID,','), CONCAT(RELATION,',')
from TABLE1

Cheers,
Danny
Kent OlsenData Warehouse / Database Architect

Commented:
Hi Danny,

If you're going to use that approach, quite a bit of refinement is needed.  Unless you want a lot of embedded spaces, you need to return a single column instead of letting the client format the results into columns.  Also, DB2 won't let you concatenate non-string objects.  Integers will need to be recast to string types.


select
  NAME || ',' ||
  RELATIVE || ',' ||
  CAST (NAME_ID as VARCHAR (10)) || ',' ||
  CAST (RELATIVE_ID as VARCHAR (10)) || ',' ||
  RELATION
from TABLE1;


Kent
pvsbandi

Author

Commented:
@DannyOfSweden:
   It gives me an error : SQL0440N  No authorized routine named "CONCAT" of type "FUNCTION
@Kdo :
   I need the results for further calculations into a Bigger SQL; This attempt is just a small part of it.
momi_sabag

Commented:
try concatenate instead of concat
Kent OlsenData Warehouse / Database Architect

Commented:
Hi pvsbandi,

The EXPORT command will export a query based selection.  Declaring the delimiter to be a comma relieves you of the pain of formatting the output.

  http://publib.boulder.ibm.com/tividd/td/TSLA/SC32-0835-03/en_US/HTML/sl21amst73.htm



  db2 export to myfile of del select name, relative, name_id, relative_id, {etc} from mytable


Kent
pvsbandi

Author

Commented:
@momi_sabag:

   It gives me the same error..
SQL0440N  No authorized routine named "CONCATENATE" of type "FUNCTION
Kent OlsenData Warehouse / Database Architect

Commented:
Hi pvsbandi,


DB2 "overloads" many of its built-in functions just like you can do in C++, Java, etc.  When accessing the function, DB2 searches the library for a function of the correct name with the exact number and type of arguments as in the function call.  Since there is no CONCAT or CONCATENATE function in DB2 that accepts integers as an argument, you're getting that error.

If you'll use the double-pipe form that I posted above you'll get the same error, but when you resolve the data type issues the SQL will be easier to type and read.


Kent

 
pvsbandi

Author

Commented:
@kdo,

    I need these concatenated results to show on a report, along with many other columns.It's not much use to just export this result to a file.. Please let me know if i'm not clear.
Kent OlsenData Warehouse / Database Architect

Commented:
Hi pvsbandi,

I didn't understand the need to put this into a report.  Unless you want to "reimport" the file, EXPORT probably won't be very useful.

But you still must deal with the data type issues.  The CONCAT function/operator only deals with strings.  Anything that is not a string must be converted to a CHAR or VARCHAR type before being passed to CONCAT.

select
  NAME || ',',
  RELATIVE || ',',
  CAST (NAME_ID as VARCHAR (10)) || ',',
  CAST (RELATIVE_ID as VARCHAR (10)) || ',',
  RELATION
from TABLE1;


Kent
pvsbandi

Author

Commented:
Hi Kent,

    Your SQL is simply appending a "," to the existing field values..it's not showing them in one row..
    Can you please look into it and advise?
Kent OlsenData Warehouse / Database Architect

Commented:
The first SQL writes all of the values on a single row, the second was following Danny's lead.

Try this:

select
  NAME || ',' ||
  RELATIVE || ',' ||
  CAST (NAME_ID as VARCHAR (10)) || ',' ||
  CAST (RELATIVE_ID as VARCHAR (10)) || ',' ||
  RELATION
from TABLE1;


kent
Kent OlsenData Warehouse / Database Architect

Commented:
Oh.  You might want to call RTRIM on each of the values to eliminate the trailing spaces.

select
  RTRIM (NAME) || ',' ||
  RTRIM (RELATIVE) || ',' ||
  CAST (NAME_ID as VARCHAR (10)) || ',' ||
  CAST (RELATIVE_ID as VARCHAR (10)) || ',' ||
  RELATION
from TABLE1;


Kent
pvsbandi

Author

Commented:
No, i'm not getting the expected result. Here is what i tried;attached as a code.
   The result i'm getting is :

JOSHUA,JEFFREY,123   ,346   ,BROTHER
JOSHUA,CHEYENNE,123   ,459   ,COUSIN
JOSHUA,ARYAN,123   ,548   ,FRIEND

My expected result is :

JOSHUA | JEFFREY,CHEYENNE,ARYAN | 123 | 346,459,548 | BROTHER,COUSIN,FRIEND
    

With Temp(NAME,RELATIVE,NAME_ID,RELATIVE_ID,RELATION)
AS
(SELECT NAME,RELATIVE,NAME_ID,RELATIVE_ID,RELATION FROM 
(VALUES ('JOSHUA','JEFFREY',123,346,'BROTHER')
       ,('JOSHUA','CHEYENNE',123,459,'COUSIN')
       ,('JOSHUA','ARYAN',123,548,'FRIEND')
)t0(NAME,RELATIVE,NAME_ID,RELATIVE_ID,RELATION)
)

select
  RTRIM (NAME) || ',' ||
  RTRIM (RELATIVE) || ',' ||
  CAST (NAME_ID as CHAR (6)) || ',' ||
  CAST (RELATIVE_ID as CHAR (6)) || ',' ||
  RELATION
 FROM TEMP

Open in new window

Data Warehouse / Database Architect
Commented:

Hi pvs,

You'll need to create a function to concatenate the names or use recursive SQL to do it for you.


I'm including an example of recursive SQL to show you how this is done.  It's a bit intimidating, but don't let it get you down.  If you want to go this route, post the relevant portions of the table and I'll write it for you.  Adding the non-recursive columns is trivial.


Kent

--  Create a sample table and test data

create table st (id integer, word varchar (10));
insert into st values (1, 'abc'), (1, 'def'), (1, 'ghi');
insert into st values (2, 'aaa'), (2, 'bbb'), (2, 'ccc');
insert into st values (3, '123'), (3, '456');

--  This is the recursive SQL.

WITH temp1(id, word, all_words, cnt) as
(
  SELECT id, min(word), VARCHAR(min(word),50),SMALLINT(1) FROM st a group by id
  UNION ALL
  SELECT a.id, a.word, b.all_words||','||a.word,SMALLINT(b.cnt+1)
  FROM st a, temp1 b
  WHERE a.id = b.id
    AND a.word > b.word
    AND a.word = (select min(c.word) from st c where c.id = b.id and c.word > b.word) 
)
SELECT id, d.all_words FROM temp1 d
where d.cnt = (Select max(cnt) from temp1 e where d.id = e.id);

Open in new window

pvsbandi

Author

Commented:
Thanks! That helps.