Long to wide - SQL [duplicate]

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.

Answer 1

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

Answer 2

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