Topic
  • 5 replies
  • Latest Post - ‏2013-03-19T09:45:35Z by deepaksamal
deepaksamal
deepaksamal
71 Posts

Pinned topic columns to be displayed in a row

‏2013-03-13T06:55:38Z |
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 ?

Please suggest.

Deepak Samal
  • deepaksamal
    deepaksamal
    71 Posts

    Re: columns to be displayed in a row

    ‏2013-03-14T06:34:19Z  
    if this question is confusing, I am just giving another example

    I have a table 'Table1' with 2 column and the values are as below

    ID DEPTNO
    • ----
    1 'A'
    1 'B'
    1 'C'

    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.

    Deepak Samal
  • jmueller
    jmueller
    71 Posts

    Re: columns to be displayed in a row

    ‏2013-03-14T08:26:23Z  
    if this question is confusing, I am just giving another example

    I have a table 'Table1' with 2 column and the values are as below

    ID DEPTNO
    • ----
    1 'A'
    1 'B'
    1 'C'

    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.

    Deepak Samal
    Hi Deepak,

    you need the listagg function:

    CREATE TABLE lagg(
    id INT,
    deptno VARCHAR(20)
    );

    INSERT INTO lagg VALUES
    (1, 'A'),
    (1, 'B'),
    (1, 'C');

    select id, deptno from lagg;

    SELECT
    id,
    LISTAGG(deptno,'-')
    FROM lagg
    GROUP BY id
    ;

    drop table lagg;

    Best regards
    Joachim
  • jmueller
    jmueller
    71 Posts

    Re: columns to be displayed in a row

    ‏2013-03-14T08:30:19Z  
    • jmueller
    • ‏2013-03-14T08:26:23Z
    Hi Deepak,

    you need the listagg function:

    CREATE TABLE lagg(
    id INT,
    deptno VARCHAR(20)
    );

    INSERT INTO lagg VALUES
    (1, 'A'),
    (1, 'B'),
    (1, 'C');

    select id, deptno from lagg;

    SELECT
    id,
    LISTAGG(deptno,'-')
    FROM lagg
    GROUP BY id
    ;

    drop table lagg;

    Best regards
    Joachim
    Additional note:

    Statement above is for DB2 Version 9.7 FP4.

    You can use xmlgroup when your fixpack is < FP4:

    SELECT
    dept,
    SUBSTR(XMLCAST(XMLGROUP(',' || name AS a
    ORDER BY name)
    AS VARCHAR(60)), 2) AS Names,
    avg(salary) as avgsal
    FROM emp
    GROUP BY dept;

    Best regards
    Joachim
  • jmueller
    jmueller
    71 Posts

    Re: columns to be displayed in a row

    ‏2013-03-14T08:33:01Z  
    • jmueller
    • ‏2013-03-14T08:30:19Z
    Additional note:

    Statement above is for DB2 Version 9.7 FP4.

    You can use xmlgroup when your fixpack is < FP4:

    SELECT
    dept,
    SUBSTR(XMLCAST(XMLGROUP(',' || name AS a
    ORDER BY name)
    AS VARCHAR(60)), 2) AS Names,
    avg(salary) as avgsal
    FROM emp
    GROUP BY dept;

    Best regards
    Joachim
    Sorry, statement is wrong:

    SELECT
    id,
    SUBSTR(XMLCAST(XMLGROUP('-' || deptno AS a
    ORDER BY deptno)
    AS VARCHAR(60)), 2) AS deptno
    FROM lagg
    GROUP BY id;

    This is the right one.

    Best regards
    Joachim
  • deepaksamal
    deepaksamal
    71 Posts

    Re: columns to be displayed in a row

    ‏2013-03-19T09:45:35Z  
    Thanks