CREATE TABLE #tbl( Date DATE, Person VARCHAR(1), Number INT

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #tbl(
	Date	DATE,
	Person	VARCHAR(1),
	Number	INT
);
INSERT INTO #tbl VALUES
('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);


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

DROP TABLE #tbl;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results