Dynamic Pivot Table with date column

Question

I have a table in which information about a stock for an items are available.

This is how the table looks like:

ITEMCODE  DATE        INSTOCK
-----------------------------
ABC001    2019-01-04    10
ABC001    2019-02-04    10
ABC001    2019-03-04    10
ABC001    2019-04-04     5
ABC001    2019-05-04     5

Is it possible to get output like this:

Itemcode   01/04/2019    02/04/2019  03/04/2019  04/04/2019   05/04/2019
-------------------------------------------------------------------------    
ABC001        10             10         10           5             5

This is the query which i have used...

SELECT T0.ITEMCODE,T0.INSTOCK 
FROM DBO.TABLE_2 T0
WHERE T0.DATE >='[%0]'AND T0.DATE <= '[%1]'

But after doing some research, I have found out its possible using pivot table...

How to modify my query in order to get the desired output

If you see my query...In where condition I have provided two conditions. Based on these conditions only query output should work... — DeePak, May 01 '19 at 19:57
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Andrea, May 05 '19 at 14:20

score 3 · Accepted Answer · answered May 01 '19 at 20:09

I was able to get it with the code below, you just need to replace #table with your table name. Also ignore the first part of the code that sets up the table.

There are similar questions/answers here: SQL Server dynamic PIVOT query?

-------------------------------------------------------------------
IF OBJECT_ID('tempdb..#table') IS NOT  NULL
BEGIN
    DROP TABLE #table
END

CREATE TABLE #table(ITEMCODE VARCHAR(10),DATE date,INSTOCK int)

insert into #table values('ABC001','2019-01-04',10)
insert into #table values('ABC001','2019-02-04',10)
insert into #table values('ABC001','2019-03-04',10)
insert into #table values('ABC001','2019-04-04',5)
insert into #table values('ABC001','2019-05-04',5)
-------------------------------------------------------------------

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

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

set @query = 'SELECT ITEMCODE, ' + @cols + ' from 
            (
                select ITEMCODE
                    , DATE
                    , INSTOCK
                from #table
           ) x
            pivot 
            (
                 sum(INSTOCK)
                for DATE in (' + @cols + ')
            ) p '


execute(@query)

Dynamic Pivot Table with date column

1 Answers1