Gui Phuot

0

Please login or register to vote for this query.

(click on this box to dismiss)

Engineering

Q&A for professionals and students of engineering

CREATE TABLE #temp
    ([itemid] int, [date] date, [qty] int)
;
    
INSERT INTO #temp
    ([itemid], [date], [qty])
VALUES
    (102, '2020-10-10', 96),
    (101, '2020-10-10', 138),
    (105, '2020-10-10', 37),
    (102, '2020-10-10', 59),
    (101, '1992-11-02', 282),
    (102, '1992-11-02', 212),
    (105, '1992-11-02', 78),
    (109, '1992-11-02', 97),
    (105, '2019-01-01', 60),
    (102, '2019-01-01', 123),
    (101, '2019-01-01', 220),
    (109, '2019-01-01', 87)
;

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.date) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = 'select *
from 
(
  select itemid, date, qty
  from #temp
) src
pivot
(
  sum(qty)
  for date in (' + @cols + ')
) piv';

execute(@query);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results