Convert Rows to Columns with Comma Delimiter

[Papya013]

Hi Team,

I am working on of the migration project and the table counts are validated once DB2 tables are loaded to Cloud platform.

The integration tool expects the counts of each table in a row format with a comma delimiter.

Code: Select all

SELECT COUNT(*) FROM SCHEMA_NAME.TABLE1
UNION ALL
SELECT COUNT(*) FROM SCHEMA_NAME.TABLE2
UNION ALL
SELECT COUNT(*) FROM SCHEMA_NAME.TABLE3
 

The above Query results are as mentioned below

Code: Select all

120
130
140

Could you please help me to get this result in below mentioned format.

120,130,140

Thanks in advance !

[sergeyken]

One of straightforward solutions might be like this

Code: Select all

SELECT T1.CNT,
    ',', T2.CNT,
    ',', T3.CNT
FROM (  
   SELECT COUNT(*) CNT, 1 ID
   FROM SCHEMA_NAME.TABLE1
)                    T1,            
(  
   SELECT COUNT(*) CNT, 1 ID
   FROM SCHEMA_NAME.TABLE2
)                    T2,
(  
   SELECT COUNT(*) CNT, 1 ID
   FROM SCHEMA_NAME.TABLE3
)                    T3
WHERE T1.ID = T2.ID
  AND T1.ID = T3.ID
;
 

But I doubt if this approach would help in any migration project...

[Papya013]

Hi Team,

This worked for now. Thank you very much !!!!!

[sergeyken]

Hi Team,

This worked for now. Thank you very much !!!!!

I did not mention this explicitly, but it looks like you plan to create data in CSV format (but including only one single line - looks strange for migration project).

Nevertheless, any CSV format assumes character only data fields. Your SQL statement will produce mixed data, binary + character (unless you run it under SPUFI, or similar). Most likely, you would need to convert all extracted non-character fields to character format, using various SQL functions, like CAST, and others.