Convert Rows to Columns with Comma Delimiter



IBM's flagship relational database management system

Convert Rows to Columns with Comma Delimiter

Postby Papya013 » Wed Mar 24, 2021 3:09 pm

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.

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

120
130
140


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

120,130,140

Thanks in advance !
Papya013
 
Posts: 35
Joined: Wed Nov 06, 2013 6:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Rows to Columns with Comma Delimiter

Postby sergeyken » Wed Mar 24, 2021 8:39 pm

One of straightforward solutions might be like this

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... :(
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 153
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 3 times
Been thanked: 13 times

Re: Convert Rows to Columns with Comma Delimiter

Postby Papya013 » Thu Mar 25, 2021 9:32 am

Hi Team,

This worked for now. Thank you very much !!!!!
Papya013
 
Posts: 35
Joined: Wed Nov 06, 2013 6:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Convert Rows to Columns with Comma Delimiter

Postby sergeyken » Thu Mar 25, 2021 7:12 pm

Papya013 wrote: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.
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 153
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 3 times
Been thanked: 13 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post