creating dynamic pivot using sql

Question

i don't want to use static pivot because my values are dynamic, here is what my table look like

ItemName    Date    Qty
Tomato  11/29/2015  20
Tomato  11/30/2015  15
Apple   12/2/2015   41
Mango   12/3/2015   23
Onion   12/4/2015   11

but i want to look like this but i can't hard code the ItemName and the Date because they are always dynamic.

score 1 · Accepted Answer · answered Dec 11 '15 at 08:18

Try like Below Query

--Sample Table Creation

 create table veg
 ( itemname varchar(50),
   [date]   date,
    qty     int
 )

insert into veg
values
('Tomato',  '11/29/2015',  20),
('Tomato',  '11/30/2015' , 15),
('Apple' ,  '12/2/2015'  ,41),
('Mango' ,  '12/3/2015'  , 23),
('Onion' ,   '12/4/2015' ,   11)

--dynamic sql

   DECLARE @col AS NVARCHAR(MAX)
   DECLARE @val AS NVARCHAR(MAX)

   SET @col = STUFF((SELECT distinct ',' + QUOTENAME(c.date)  
               FROM veg c
               FOR XML PATH('')
               ),1,1,'')

   --select @col

   set @val  = 'SELECT itemname, ' + @col + ' from 
               (
            SELECT itemname,date,qty
               FROM veg
               ) SOURCE
               PIVOT 
               (
               SUM(qty)
               FOR date IN (' + @col + ')
               ) p '


   exec (@val)

creating dynamic pivot using sql

1 Answers1