By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T(
userName VARCHAR(100),
Price int,
Dt DATE
);
INSERT INTO T VALUES ('Tom',100,'2017-01-01');
INSERT INTO T VALUES ('Amy',200,'2017-01-02');
INSERT INTO T VALUES ('Tom',1311,'2017-01-03');
INSERT INTO T VALUES ('Tom',122,'2017-03-01');
INSERT INTO T VALUES ('Tom',111,'2017-04-01');
INSERT INTO T VALUES ('Amy',232,'2017-05-01');
INSERT INTO T VALUES ('Tom',2312,'2017-05-02');
INSERT INTO T VALUES ('Tom',23,'2017-05-03');
SELECT (SELECT distinct ',SUM(CASE WHEN Dt = '''+ CAST(Dt AS VARCHAR(10)) +''' THEN Price ELSE 0 END) AS ' + QUOTENAME(Dt)
FROM T
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
| (No column name) |
|---|
| ,SUM(CASE WHEN Dt = '2017-01-01' THEN Price ELSE 0 END) AS [2017-01-01],SUM(CASE WHEN Dt = '2017-01-02' THEN Price ELSE 0 END) AS [2017-01-02],SUM(CASE WHEN Dt = '2017-01-03' THEN Price ELSE 0 END) AS [2017-01-03],SUM(CASE WHEN Dt = '2017-03-01' THEN Price ELSE 0 END) AS [2017-03-01],SUM(CASE WHEN Dt = '2017-04-01' THEN Price ELSE 0 END) AS [2017-04-01],SUM(CASE WHEN Dt = '2017-05-01' THEN Price ELSE 0 END) AS [2017-05-01],SUM(CASE WHEN Dt = '2017-05-02' THEN Price ELSE 0 END) AS [2017-05-02],SUM(CASE WHEN Dt = '2017-05-03' THEN Price ELSE 0 END) AS [2017-05-03] |