| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-10-09, 00:57
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 7
|
|
|
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! 
|
|
12-10-09, 02:39
|
|
Registered User
|
|
Join Date: Aug 2009
Posts: 23
|
|
recursive equi-join of table to itself on ID
|
|
12-10-09, 10:25
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
|
|
This is an FAQ: Search for "pivot" tables.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
|
|
12-10-09, 10:43
|
|
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!
|
|
12-12-09, 21:08
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
12-17-09, 01:16
|
|
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!
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Transposing columnar data into row
