Home > Blockchain >  Converting rows into columns in db2 sql
Converting rows into columns in db2 sql

Time:09-13

I have table subs_details sample data and columns like:

ID Number
1 4579
2 5678
3 6678
2 7803
1 5479
3 5779

I want to convert it into below output:

ID1 id1_number id2 id2_number id3 id3_number
1 4579 2 5678 3 6678
1 5479 2 7803 3 5779

Please help in this

CodePudding user response:

Please help to making query to produce expected output

CodePudding user response:

You may run it as is.

WITH 
  MYTAB (ID, Number) AS
(
VALUES
  (1, 4579)
, (2, 5678)
, (3, 6678)
, (2, 7803)
, (1, 5479)
, (3, 5779)
)
, MYTAB_NUMBERED AS
(
SELECT T.*, ROW_NUMBER () OVER (PARTITION BY ID) AS RN_
FROM MYTAB T
)
SELECT 
  1 AS ID1, T1.NUMBER AS ID1_NUMBER
, 2 AS ID2, T2.NUMBER AS ID2_NUMBER  
, 3 AS ID3, T3.NUMBER AS ID3_NUMBER
FROM (SELECT DISTINCT RN_ FROM MYTAB_NUMBERED) N
LEFT JOIN MYTAB_NUMBERED T1 ON T1.RN_ = N.RN_ AND T1.ID = 1
LEFT JOIN MYTAB_NUMBERED T2 ON T2.RN_ = N.RN_ AND T2.ID = 2
LEFT JOIN MYTAB_NUMBERED T3 ON T3.RN_ = N.RN_ AND T3.ID = 3
ID1 ID1_NUMBER ID2 ID2_NUMBER ID3 ID3_NUMBER
1 4,579 2 5,678 3 6,678
1 5,479 2 7,803 3 5,779
  • Related