is there a SQl query i can use to put the multiple column values in a row ..
For ex: My requirement is to generate a sql statements like below.
Select Col1, Col2, Col3 from Table1;
I know from sysibm.syscolumns system table, i can get all the table name and respective column names. But how to generate/form the above sql query ?
This topic has been locked.
5 replies Latest Post - 2013-03-19T09:45:35Z by deepaksamal
Pinned topic columns to be displayed in a row
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-03-19T09:45:35Z at 2013-03-19T09:45:35Z by deepaksamal
Re: columns to be displayed in a row2013-03-14T06:34:19Z in response to deepaksamalif this question is confusing, I am just giving another example
I have a table 'Table1' with 2 column and the values are as below
I want to display something like below
Select ID, ? from Table1;
1, 'A'-'B'-'C' (Basically i need to display all the column values in a single rows)
Please guide me with the query.
Re: columns to be displayed in a row2013-03-14T08:26:23Z in response to deepaksamalHi Deepak,
you need the listagg function:
CREATE TABLE lagg(
INSERT INTO lagg VALUES
select id, deptno from lagg;
GROUP BY id
drop table lagg;
Re: columns to be displayed in a row2013-03-14T08:30:19Z in response to jmuellerAdditional note:
Statement above is for DB2 Version 9.7 FP4.
You can use xmlgroup when your fixpack is < FP4:
SUBSTR(XMLCAST(XMLGROUP(',' || name AS a
ORDER BY name)
AS VARCHAR(60)), 2) AS Names,
avg(salary) as avgsal
GROUP BY dept;
Re: columns to be displayed in a row2013-03-14T08:33:01Z in response to jmuellerSorry, statement is wrong:
SUBSTR(XMLCAST(XMLGROUP('-' || deptno AS a
ORDER BY deptno)
AS VARCHAR(60)), 2) AS deptno
GROUP BY id;
This is the right one.