Wilz Wilz - 1 month ago 10
SQL Question

SQL Server Query with Pivot table

I have an SQL query that returns the following results:

Item Count Date
-----------------------
item1 10 01MAR14
item1 5 02MAR14
Item2 5 01MAR14
item3 12 03MAR14
item2 12 04MAR14
item3 2 05MAR14
item1 3 05MAR14


But I need the results as follows:

Item 05MAR14 04MAR14 03MAR14 Before-03MAR14
----------------------------------------------
item1 3 0 0 15
item2 0 12 0 5
item3 2 0 12 0


I'm using SQL Server 2008R2.

Answer

Just a simple idea to provide you the knowledge on Pivoting. I've replaced your date format with mine but it is better to use dynamic query for pivoting.

CREATE TABLE [dbo].[tblTest](
    [Item] [varchar](50) NOT NULL,
    [Count] [int] NOT NULL,
    [Date] [date] NOT NULL
) 


insert into tblTest values('item1', 10, '01MAR14')
insert into tblTest values('item1', 5 , '02MAR14')
insert into tblTest values('Item2', 5 , '01MAR14')
insert into tblTest values('item3', 12, '03MAR14')
insert into tblTest values('item2', 12, '04MAR14')
insert into tblTest values('item3', 2 , '05MAR14')
insert into tblTest values('item1', 3 , '05MAR14')


SELECT Item, IsNull([2014-03-05], 0) as [2014-03-05], Isnull([2014-03-04], 0) [2014-03-04], IsNull([2014-03-03],0) [2014-03-03], ISNULL([2014-03-02], 0) as 'Before[2014-03-01]'
    FROM (
        SELECT 
            Item, case when [Date] < '2014-03-03' then '2014-03-02' else [DATE] end as [Date],  [Count]
        FROM tblTest
    ) AS s
    PIVOT
    (
        sum([Count])
        FOR [Date] IN ([2014-03-05],[2014-03-04],[2014-03-03], [2014-03-02])
    )AS pivotTable

Output

Comments