How to convert rows to comma separated values

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

Hi pvsbandi,

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


Good Luck,
Kent

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

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

@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.

try concatenate instead of concat

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

@momi_sabag:

   It gives me the same error..
SQL0440N  No authorized routine named "CONCATENATE" of type "FUNCTION

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

 

@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.

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

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?

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

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

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

Select allOpen in new window

Thanks! That helps.