Multiple rows into variable number of columns [duplicate]

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.

Which dbms are you using? (The two previous comments assume MS SQL Server.) — jarlh
– jarlh, Commented Apr 15, 2019 at 7: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
– Thorsten Kettner, Commented Apr 15, 2019 at 8:00

DxTx · Accepted Answer · 2019-04-15 08:13:12Z

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');

Collectives™ on Stack Overflow

Multiple rows into variable number of columns [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related