Multiple rows into variable number of columns

Question

I am working with a SQL problem where I have a variable number of rows with corresponding values that I need to put into columns.

Input table:

id | value
----------
1  | a
1  | b
2  | c
3  | d
3  | e
3  | f

Expected output table:


id | value1 | value2 | value3 | value4 
--------------------------------------
1  | a      | b      | null   | null 
2  | c      | null   | null   | null 
3  | d      | e      | f      | null

Side notes: The value column is the primary key of the input table. The maximum number of output columns should be fixed to 4.

Try this: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Dov Vachman, Apr 15 '19 at 07:46
Which dbms are you using? (The two previous comments assume MS SQL Server.) — jarlh, Apr 15 '19 at 07:47
This is probably something I'd do in the GUI layer, i.e. your app or Website. This should be a rather simple loop. — Thorsten Kettner, Apr 15 '19 at 08:00

DxTx · Accepted Answer · 2019-04-15T08:13:12.773

You can use SQL Pivot technique for this. Try this query.

SELECT *
FROM   (SELECT *, Row_number() OVER (partition BY id ORDER BY id) AS rn
        FROM   Input) src
       PIVOT ( Max([value])
             FOR rn IN ([1],[2],[3],[4]) ) piv;

Result

+----+---+------+------+------+
| id | 1 |  2   |  3   |  4   |
+----+---+------+------+------+
|  1 | a | b    | NULL | NULL |
|  2 | c | NULL | NULL | NULL |
|  3 | d | e    | f    | NULL |
+----+---+------+------+------+

Table Schema and Data

CREATE TABLE Input
  (
     id    INT,
     [value] NVARCHAR(10)
  );

INSERT INTO Input
VALUES      (1, 'a'),
            (1, 'b'),
            (2, 'c'),
            (3, 'd'),
            (3, 'e'),
            (3, 'f');

Multiple rows into variable number of columns

1 Answers1