Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: Transposing columnar data into row

    I have a table that contains the following data:

    A B C D
    1 123 135 AEC
    1 Z13 246 DFY
    2 589 987 GHT
    3 98Y 258 JKL
    3 5P8 9174 THF
    4 708 7192 TRU
    4 AT3 313 GFY
    5 51G 111 LIM

    I need a table that looks like this:
    X
    1, 123, 135, AEC, Z13, 246, DFY
    2, 589, 987, GHT
    3, 98Y, 258, JKL, 5P8, 9174, THF
    4, 708, 7192, TRU, AT3, 313, GFY
    5, 51G, 111, LIM

    Any idea how to go about doing it? Any assistance will be much appreciated.

    Thanks!

  2. #2
    Join Date
    Aug 2009
    Posts
    23
    recursive equi-join of table to itself on ID

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This is an FAQ: Search for "pivot" tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Dec 2009
    Posts
    7
    Thanks guys. Anyway, I've managed to solve this using Excel.
    But of cos I will try out the methods in this thread too!

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the maximum number of duplicated values of A is not fixed,
    XMLCAST( XMLGROUP( ... ) AS VARCHAR(nnn) ) would be an example.

    Like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
     table_a(A, B, C, D ) AS (
    VALUES
     (1, '123',  135, 'AEC')
    ,(1, 'Z13',  246, 'DFY')
    ,(2, '589',  987, 'GHT')
    ,(3, '98Y',  258, 'JKL')
    ,(3, '5P8', 9174, 'THF')
    ,(3, '6Q9',   77, 'UVW')
    ,(3, '7R0', 4321, 'XYZ')
    ,(4, '708', 7192, 'TRU')
    ,(4, 'AT3',  313, 'GFY')
    ,(4, 'GEM',  131, 'BNX')
    ,(5, '51G',  111, 'LIM')
    )
    SELECT a ||
           XMLCAST( XMLGROUP( ', ' AS s1, b, ', ' AS s2, c, ', ' AS s3, d )
                    AS VARCHAR(100) ) AS x
      FROM table_a
     GROUP BY
           a
     ORDER BY
           a
    ;
    ------------------------------------------------------------------------------
    
    X                                                                                                              
    ---------------------------------------------------------------------------------------------------------------
    1, 123, 135, AEC, Z13, 246, DFY                                                                                
    2, 589, 987, GHT                                                                                               
    3, 98Y, 258, JKL, 5P8, 9174, THF, 6Q9, 77, UVW, 7R0, 4321, XYZ                                                 
    4, 708, 7192, TRU, AT3, 313, GFY, GEM, 131, BNX                                                                
    5, 51G, 111, LIM                                                                                               
    
      5 record(s) selected.
    Last edited by tonkuma; 12-12-09 at 22:12.

  6. #6
    Join Date
    Dec 2009
    Posts
    7
    Thanks Tonkuma. As I am using i5/OS v5r4, i wasn't able to execute the select statement as I think XML functions are limited on this version.

    Cheers!

Posting Permissions

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