Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    32

    Unanswered: Transformation - Columns to lines based on key

    Morning / afternoon / evening ladies and gentlemen

    As stated in the subject line, I have a table of around 1000 values for a composite key. This looks somewhat like this:


    KEY1 KEY2 KEY3 COL VAL

    AAA BBB CCC A1 1
    AAA BBB CCC A2 2
    AAA BBB CCC A3 3
    ...
    ...
    ...
    AAA BBB CCC A1000 1000

    I need to pivot this data on the composite key which is defined on the only the first 3 columns.

    The tricky part begins now. The data from the VAL column needs to be mapped to a destination table where the name of the column is found in the COL column.
    The desired result would thus be:


    KEY1 KEY2 KEY3 A1 A2 A3 ....... A1000

    AAA BBB CCC 1 2 3 ...... 1000

    Is this achievable in DB2 LUW 9.5?

    Thanks

    Tony

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    yes, you can do it, but it would require to build such a query dynamically.
    I mean firstly you can scan the 1-st result set and then build the final result set using these VALs.
    Regards,
    Mark.

  3. #3
    Join Date
    Apr 2012
    Posts
    32
    Quote Originally Posted by mark.b View Post
    Hi,

    yes, you can do it, but it would require to build such a query dynamically.
    I mean firstly you can scan the 1-st result set and then build the final result set using these VALs.
    Thanks Mark.
    Can you please give me an example of one insert statement based on the data above so that I can derive the others from the same?

    Cheers

    Tony

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    select key1, key2, key3
    , max(case col when 'A1' then val end) A1
    , max(case col when 'A2' then val end) A2
    , max(case col when 'A3' then val end) A3
    --, ...
    , max(case col when 'A1000' then val end) A1000
    from table(values
      ('AAA', 'BBB', 'CCC', 'A1', 1)
    , ('AAA', 'BBB', 'CCC', 'A2', 2)
    , ('AAA', 'BBB', 'CCC', 'A3', 3)
    --, ... 
    , ('AAA', 'BBB', 'CCC', 'A1000', 1000)
    ) t (KEY1, KEY2, KEY3, COL, VAL)
    group by key1, key2, key3
    
    
    
     KEY1 KEY2 KEY3 A1 A2 A3 A1000
     ---- ---- ---- -- -- -- -----
     AAA  BBB  CCC   1  2  3  1000
    Regards,
    Mark.

  5. #5
    Join Date
    Apr 2012
    Posts
    32
    Thanks man.
    Works Great...:-)

  6. #6
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    you may be also interested in this
    https://www.ibm.com/developerworks/c...bles56?lang=en
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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