# Thread: Transposing columnar data into row

1. Registered User
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. Registered User
Join Date
Aug 2009
Posts
23
recursive equi-join of table to itself on ID

3. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
This is an FAQ: Search for "pivot" tables.

4. Registered User
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. Registered User
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 21:12.

6. Registered User
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
•