Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2003
    Posts
    34

    Unanswered: Db2 row to column

    My requirement is to convert all the rows for a given key to a single row. Can somebody help me in creating an sql to do this. Can we do this using the CASE statement without any hardcoding. Any ideas ?

    TABLE1

    key1 VALUE
    X00003 00344
    X00003 00345
    X00003 00346
    X00003 00241
    X00003 00334
    X00004 00344
    X00004 00345
    X00004 00346
    X00004 00241
    X00004 00334


    Ouput

    X00003 00241 00334 00344 00345 00346
    X00004 00241 00334 00344 00345 00346

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think there are many articles related to this issue (I myself posted sometimes).

    Please search in this forum with keywords like "row to column", "pivot unpivot", so on ...

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've built a SQL Fiddle that shows how to do this in Microsoft SQL Server 2012. SQL Fiddle doesn't support DB2 (yet anyway), and I don't have a copy of DB2 handy to test that it will work identically, but I'm pretty sure that it will. If not, you'll at least have the idea working in a place where you can change it to suit your needs.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2003
    Posts
    34
    @tonkuma, thanks for your response.
    I looked at the row to column examples and saw the example using CASE statement, but my problem here is i don't know how many values can be for a particular key. There can be 10 for one key and there can be 20 for the second key. I don't want to hard code those values in the CASE statement. Hope I am clear on my requirement.

    In my same example what if I have another key with the below values

    X00005 00344
    X00005 00345
    X00005 00346
    X00005 00241
    X00005 00334
    X00005 00400
    X00005 00401
    X00005 00402
    X00005 00403

  5. #5
    Join Date
    Jul 2003
    Posts
    34
    @ Pat Phelan , Thanks for your response.

    This is exactly what I want. I tried it in DB2 and it worked.

    Thanks a lot for your time.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    In a single SQL query, number of result items(columns) was fixed.

    If muximum number of values for a particular key was known,
    repeat CASE expressions until the muximum number of values.

    If muximum number of values for a particular key was not known,
    I have two ideas.
    (1) Consruct dynamic SQL query by looking for muximum number of values for keys.
    (2) List values in a column.

    Example of (2):
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     table1
    ( key , value ) AS (
    VALUES
      ( 'X00003' , '00344' )
    , ( 'X00003' , '00345' )
    , ( 'X00003' , '00346' )
    , ( 'X00003' , '00241' )
    , ( 'X00003' , '00334' )
    , ( 'X00004' , '00344' )
    , ( 'X00004' , '00345' )
    , ( 'X00004' , '00346' )
    , ( 'X00004' , '00241' )
    , ( 'X00004' , '00334' )
    , ( 'X00004' , '00451' )
    , ( 'X00004' , '00442' )
    , ( 'X00004' , '00433' )
    )
    SELECT key
         , LISTAGG(value , ' ') WITHIN GROUP(ORDER BY value) AS values
     FROM  table1
     GROUP BY
           key
    ;
    ------------------------------------------------------------------------------
    
    KEY    VALUES                                                                 
    ------ -----------------------------------------------------------------------
    X00003 00241 00334 00344 00345 00346                                          
    X00004 00241 00334 00344 00345 00346 00433 00442 00451                        
    
      2 record(s) selected.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by gopurs View Post
    @ Pat Phelan , Thanks for your response.

    This is exactly what I want. I tried it in DB2 and it worked.

    Thanks a lot for your time.
    gopurs,

    Please publish what you did(your tried query in DB2).

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There in lies the beauty of a SQL Fiddle! With the source code and an environment where you can modify and experiement, you can create a new version to meet specific requirements (as you can see I've done).

    Note that this solution violates first normal form. That violation will definitely cause you problems if you need to build upon it further.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tonkuma View Post
    (2) List values in a column.
    Good idea, I'd forgotten about ListAgg() due to it not being available on most platforms. Check my SQL Fiddle for a portable solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jul 2003
    Posts
    34
    I used the same sql what Pat Phelan gave to me in DB2

    ; WITH cte AS (
    SELECT bar, baz
    , Row_Number() OVER (PARTITION BY bar ORDER BY baz) AS rn
    FROM foo
    ) SELECT
    bar
    , Min(CASE WHEN 1 = rn THEN baz END) AS c1
    , Min(CASE WHEN 2 = rn THEN baz END) AS c2
    , Min(CASE WHEN 3 = rn THEN baz END) AS c3
    , Min(CASE WHEN 4 = rn THEN baz END) AS c4
    , Min(CASE WHEN 5 = rn THEN baz END) AS c5
    , Min(CASE WHEN 6 = rn THEN baz END) AS c6
    FROM cte
    GROUP BY cte.bar;

  11. #11
    Join Date
    Jul 2003
    Posts
    34
    Pat Phelan,

    Can we remove the duplicates in your above query. Key X00003 and X00004 have the same values and I need only one. How will I use distinct here?

  12. #12
    Join Date
    Jul 2003
    Posts
    34
    Pat Phelan,

    Thanks for your query.

    I tried your second SQL fiddle sql in DB2 and I am getting error around RN = ROW_NUMBER(). I am not sure whether DB2 supports that. Any idea?

    Gopu

  13. #13
    Join Date
    Jul 2003
    Posts
    34
    tonkuma,

    I tried your same query in DB2 version 10 for Z/OS and I get the following error

    WITH table1
    ( KEY , VALUE ) AS (
    VALUES
    ( 'X00003' , '00344' )
    , ( 'X00003' , '00345' )
    , ( 'X00003' , '00346' )
    , ( 'X00003' , '00241' )
    , ( 'X00003' , '00334' )
    , ( 'X00004' , '00344' )
    , ( 'X00004' , '00345' )
    , ( 'X00004' , '00346' )
    , ( 'X00004' , '00241' )
    , ( 'X00004' , '00334' )
    , ( 'X00005' , '00344' )
    , ( 'X00005' , '00345' )
    , ( 'X00005' , '00346' )
    , ( 'X00005' , '00241' )
    , ( 'X00005' , '00334' )
    , ( 'X00005' , '00400' )
    )
    SELECT MAX(KEY) AS RESULT
    FROM (SELECT KEY
    , LISTAGG(VALUE , ',') WITHIN GROUP(ORDER BY VALUE) AS VALUES
    FROM table1
    GROUP BY KEY
    )
    GROUP BY VALUES


    SQL error at or before TABLE1
    (line 1, position 6).

    Reference line:
    ==>WITH TABLE1

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gopurs View Post
    Pat Phelan,

    Can we remove the duplicates in your above query. Key X00003 and X00004 have the same values and I need only one. How will I use distinct here?
    The DISTINCT clause is neither necessary nor desirable. I've modified the SQL Fiddle to fix this problem too.
    Quote Originally Posted by gopurs View Post
    Pat Phelan,

    Thanks for your query.

    I tried your second SQL fiddle sql in DB2 and I am getting error around RN = ROW_NUMBER(). I am not sure whether DB2 supports that. Any idea?

    Gopu
    Yes, I have an idea. See the revised SQL Fiddle.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by gopurs View Post
    tonkuma,

    I tried your same query in DB2 version 10 for Z/OS and I get the following error

    WITH table1
    ( KEY , VALUE ) AS (
    VALUES
    ( 'X00003' , '00344' )
    , ( 'X00003' , '00345' )
    , ( 'X00003' , '00346' )
    , ( 'X00003' , '00241' )
    , ( 'X00003' , '00334' )
    , ( 'X00004' , '00344' )
    , ( 'X00004' , '00345' )
    , ( 'X00004' , '00346' )
    , ( 'X00004' , '00241' )
    , ( 'X00004' , '00334' )
    , ( 'X00005' , '00344' )
    , ( 'X00005' , '00345' )
    , ( 'X00005' , '00346' )
    , ( 'X00005' , '00241' )
    , ( 'X00005' , '00334' )
    , ( 'X00005' , '00400' )
    )
    SELECT MAX(KEY) AS RESULT
    FROM (SELECT KEY
    , LISTAGG(VALUE , ',') WITHIN GROUP(ORDER BY VALUE) AS VALUES
    FROM table1
    GROUP BY KEY
    )
    GROUP BY VALUES


    SQL error at or before TABLE1
    (line 1, position 6).

    Reference line:
    ==>WITH TABLE1
    DB2 for z/OS doesn't support VALUES row(s) constructor except in INSERT statement.
    So, make a table, like you did in another post ...
    Quote Originally Posted by gopurs View Post
    ...
    We have DB2 version 10 for Z/OS. ...

    Also , I changed the query to retrieve the data from the table instead of hardcoding.

    ...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •