I have table subs_details sample data and columns like:

I want to convert it into below output:

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