T- SQL Real Time Scenarios 2

In this scenario, we have one table, where we need to generate a pivot for showing month wise transaction counts for each category:
Source data:

Date1	Category	Amount
10/1/2015	ABC	1000
2/1/2015	DEF	500
5/1/2015	GHI	800
2/10/2015	DEF	700
3/1/2015	ABC	1100
3/1/2015	XXX	1100
1/1/2014	XXX	1000

 Below desired output need to generate by above data:

Category	Dec-15	Nov-15	Oct-15	Sep-15	Aug-15	Jul-15	Jun-15	May-15	Apr-15	Mar-15	Feb-15	Jan-15
ABC	0	0	0	0	0	0	0	0	0	1	0	1
DEF	0	0	0	0	0	0	0	0	0	0	2	0
GHI	1	0	0	0	0	0	0	0	0	0	0	0
XXX	0	0	0	0	0	0	0	0	0	1	0	1

So first solution comes in the mind is to use pivot function of SQL but by pivot function column will be coming for only those dates which are available in the data as:

Category	Dec-15	Mar-15	Feb-15	Jan-15
ABC	0	1	0	1
DEF	0	0	2	0
GHI	1	0	0	0
XXX	0	1	0	1

But we have to show all months, either the data is available in the table or not, for example we need to show 0 as count of transaction for those month which are not available in the table (from Nov to Apr, data is not available in the table).

So First I have designed a T-SQL query by using case statement to generate such output as:

First I have created a temp table as:

create table #temp1

 (

     date1 datetime,

     category varchar(3),

     amount money

 )

 insert into #temp1 values ('10/1/2015', 'ABC', 1000.00)

 insert into #temp1 values ('2/1/2015', 'DEF', 500.00)

 insert into #temp1 values ('5/1/2015', 'GHI', 800.00)

 insert into #temp1 values ('2/10/2015', 'DEF', 700.00)

 insert into #temp1 values ('3/1/2015', 'ABC', 1100.00)

 insert into #temp1 values ('3/1/2015', 'XXX', 1100.00)

 insert into #temp1 values ('1/1/2014', 'XXX', 1000.00)

SELECT * FROM #temp1

Below T-SQL query is providing complete pivot for all months for showing count of transactions for each category as:

Select Category,

Sum(CASE WHEN DATEPART(mm,date1)=1 THEN 1 ELSE 0 END) as 'Jan-15',

Sum(CASE WHEN DATEPART(mm,date1)=2 THEN 1 ELSE 0 END) as 'Feb-15',

Sum(CASE WHEN DATEPART(mm,date1)=3 THEN 1 ELSE 0 END) as 'Mar-15',

Sum(CASE WHEN DATEPART(mm,date1)=4 THEN 1 ELSE 0 END) as 'Apr-15',

Sum(CASE WHEN DATEPART(mm,date1)=5 THEN 1 ELSE 0 END) as 'May-15',

Sum(CASE WHEN DATEPART(mm,date1)=6 THEN 1 ELSE 0 END) as 'Jun-15',

Sum(CASE WHEN DATEPART(mm,date1)=7 THEN 1 ELSE 0 END) as 'Jul-15',

Sum(CASE WHEN DATEPART(mm,date1)=8 THEN 1 ELSE 0 END) as 'Aug-15',

Sum(CASE WHEN DATEPART(mm,date1)=9 THEN 1 ELSE 0 END) as 'Sep-15',

Sum(CASE WHEN DATEPART(mm,date1)=10 THEN 1 ELSE 0 END) as 'Oct-15',

Sum(CASE WHEN DATEPART(mm,date1)=11 THEN 1 ELSE 0 END) as 'Nov-15',

Sum(CASE WHEN DATEPART(mm,date1)=12 THEN 1 ELSE 0 END) as 'Dec-15'

From  #temp1

Group BY Category

Below is pivot query to generate same result:

SELECT *

        FROM

        (

         SELECT

         CONVERT(VARCHAR(11), LEFT(CONVERT(VARCHAR(11), DATE1,100),3)+'-'+RIGHT(CONVERT(VARCHAR(11),DATE1 ,100),2)) AS Date1

        ,Category

        ,amount

         FROM #temp1

         GROUP BY 

         category

        ,amount

        ,CONVERT(VARCHAR(11), LEFT(CONVERT(VARCHAR(11), DATE1,100),3)+'-'+RIGHT(CONVERT(VARCHAR(11),DATE1 ,100),2))

        ) AS DD

        PIVOT(COUNT(AMOUNT)

        FOR DATE1 IN ([Jan-15],[Feb-15],[Mar-15],[Apr-15],[May-15],[Jun-15],[Jul-15],[Aug-15],[Sep-15],[Oct-15],[Nov-15],[Dec-15]))

        AS PDate

 

NEXT: T- SQL Real Time Scenarios 3

If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!