convert lines into single row

Question

I have data like this

Col1 Col 2
100    1
100    2
100    3
100    4

need output like this

Col1 col2 col3 col4 col5 


100   1    2    3    4

need to define as new col names and populate values into these fields.

Answer 1

If you have a fixed maximum number of input rows/output columns you can use:

SELECT *
FROM   table_name
PIVOT (
  MAX(Col2)
  FOR Col2 IN (1 AS col2, 2 AS col3, 3 AS col4, 4 AS col5)
)

Which, for the sample data:

CREATE TABLE table_name (Col1, Col2) AS
SELECT 100, 1 FROM DUAL UNION ALL
SELECT 100, 2 FROM DUAL UNION ALL
SELECT 100, 3 FROM DUAL UNION ALL
SELECT 100, 4 FROM DUAL;

Outputs:

COL1	COL2	COL3	COL4	COL5
100	1	2	3	4

---

If you can have a maximum of 15 rows then:

SELECT *
FROM   (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS rn
  FROM   table_name t
)
PIVOT (
  MAX(Col2)
  FOR rn IN (
    1 AS col2,
    2 AS col3,
    3 AS col4,
    4 AS col5,
    5 AS col6,
    6 AS col7,
    7 AS col8,
    8 AS col9,
    9 AS col10,
    10 AS col11,
    11 AS col12,
    12 AS col13,
    13 AS col14,
    14 AS col15,
    15 AS col16
  )
)

or:

SELECT Col1,
       MAX(CASE rn WHEN 1 THEN Col2 END) AS Col2,
       MAX(CASE rn WHEN 2 THEN Col2 END) AS Col3,
       MAX(CASE rn WHEN 3 THEN Col2 END) AS Col4,
       MAX(CASE rn WHEN 4 THEN Col2 END) AS Col5,
       MAX(CASE rn WHEN 5 THEN Col2 END) AS Col6,
       MAX(CASE rn WHEN 6 THEN Col2 END) AS Col7,
       MAX(CASE rn WHEN 7 THEN Col2 END) AS Col8,
       MAX(CASE rn WHEN 8 THEN Col2 END) AS Col9,
       MAX(CASE rn WHEN 9 THEN Col2 END) AS Col10,
       MAX(CASE rn WHEN 10 THEN Col2 END) AS Col11,
       MAX(CASE rn WHEN 11 THEN Col2 END) AS Col12,
       MAX(CASE rn WHEN 12 THEN Col2 END) AS Col13,
       MAX(CASE rn WHEN 13 THEN Col2 END) AS Col14,
       MAX(CASE rn WHEN 14 THEN Col2 END) AS Col15,
       MAX(CASE rn WHEN 15 THEN Col2 END) AS Col16
FROM   (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS rn
  FROM   table_name t
)
GROUP BY Col1

Which both output:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10	COL11	COL12	COL13	COL14	COL15	COL16
100	1	2	3	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

db<>fiddle here

Answer 2

There is 1 more way, called Conditional aggregation to achieve the same result -

SELECT Col1,
       MAX(CASE WHEN Col2 = 1 THEN Col2 END) Col2,
       MAX(CASE WHEN Col2 = 2 THEN Col2 END) Col3,
       MAX(CASE WHEN Col2 = 3 THEN Col2 END) Col4,
       MAX(CASE WHEN Col2 = 4 THEN Col2 END) Col5
  FROM table_name
 GROUP BY Col1;

If you have 15 values, You have to pass them manually in both the queries and your result will always have 15 columns. If you want to have them dynamically transformed, refer the answer in original comment.