Long to wide - SQL

Question

I have a table that is long (for example)

    Date        Person    Number
    2015-01-03  A         4
    2015-01-04  A         2
    2015-01-05  A         3
    2015-01-03  B         5
    2015-01-04  B         6
    2015-01-05  B         7
    2015-01-03  C         1
    2015-01-04  C         3
    2015-01-05  C         4
    2015-01-05  D         4
    2015-01-04  E         1
    2015-01-05  E         3

And I need it to look like (wide):

    Date          A    B    C    D    E
    2015-01-03    4    5    1    0    0
    2015-01-04    2    6    3    0    1
    2015-01-05    3    7    4    4    3

Any help would be much appreciated. I am using Transact. Thanks.

only a, b, c, d, e or a dynamic list? – artm May 26 '16 at 02:19 — artm, May 26 '16 at 02:19
Its dynamic - a,b,c,d,e was an example. – windsormatic May 26 '16 at 02:31 — windsormatic, May 26 '16 at 02:31

dcieslak · Answer 1 · 2016-05-26T07:38:03.713

Using PIVOT:

select date, isNull([A], 0) as A, 
    isNull([B], 0) as B, 
    isNull([C], 0) as C, 
    isNull([D], 0) as D, 
    isNull([E], 0) as E 
    from
( select date, person, number 
    from tbl )  AS SourceTable
PIVOT
( max(number) for
 Person in ( [A], [B], [C], [D], [E]) ) AS PivotTable;

Dynamic version is:

DECLARE @columns NVARCHAR(MAX), @whereColumns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SET @whereColumns = N'';
SELECT @columns += N', isNull(p.' + QUOTENAME(Person) +', 0)  as ' + Person,
       @whereColumns += N', ' +QUOTENAME (Person)
  FROM (select Person from tbl group by Person) AS x;
SET @sql = N'
select date, ' +   STUFF(@columns, 1, 2, '') +
N'
    from
( select date, person, number 
    from tbl )  AS SourceTable
PIVOT
( max(number) for
 Person in ( '
  + stuff(@whereColumns, 1, 2, '')
  + ' )
) AS P '

 print @sql;
 exec (@sql);

what is the code when the column in question contains numbers, i.e. I currently have a table in long format with column `clm_cd` that contains `10, 20, ..., 50` and I want to create columns `c10, c20, ..., c50` — Marie. P., May 10 '21 at 06:01

score 5 · Answer 2 · edited Jan 18 '21 at 12:31

You can use conditional aggregation:

SELECT
    Date,
    [A] = MAX(CASE WHEN Person = 'A' THEN Number ELSE 0 END),
    [B] = MAX(CASE WHEN Person = 'B' THEN Number ELSE 0 END),
    [C] = MAX(CASE WHEN Person = 'C' THEN Number ELSE 0 END),
    [D] = MAX(CASE WHEN Person = 'D' THEN Number ELSE 0 END),
    [E] = MAX(CASE WHEN Person = 'E' THEN Number ELSE 0 END)
FROM #tbl
GROUP BY Date

If you have unknown number of Persons, then you have to do it dynamically:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql =
'SELECT
    Date' + CHAR(10) +
(
SELECT DISTINCT 
'   , MAX(CASE WHEN Person = ''' + Person + ''' THEN Number ELSE 0 END) AS ' + QUOTENAME(Person) + CHAR(10)
FROM #tbl
FOR XML PATH('')
) +
'FROM #tbl
GROUP BY Date;';

PRINT (@sql);
EXEC (@sql);

ONLINE DEMO

Long to wide - SQL

2 Answers2

Linked