Need Monthly Report in sql server query

Question

I have 2 tables(Product1, product2).

Product1                                            Product2
------------------------------            -------------------------------------
PID    ProdDesc    ProdCode                              Date        PID
------------------------------            -------------------------------------
1      Sony         s001                              2013/01/21      1
2      Samsung      sa01                              2013/02/27      1
3      LG           L001                              2013/03/14      2
4      Toshiba      T001                              2013/04/18      3
5      Philips      P001                              2013/05/30      4
                                                      2013/06/12      5

I need to generate a query that joins the data from the 2 tables and groups the results by month.

Here is my desired Output:

ProdDesc    Jan2013 Feb2013  Mar2013   Apr2013   May2013  Jun2013  Jul2013  Aug2013  Sep2013  Oct2013  Nov2013  Dec2013
--------------------------------------------------------------------------------------------------
Sony           1      1        0          0         0       0        0        0        0    
Samsung        0      0        1          0         0       0        0        0        0    
LG             0      0        0          1         0       0        0        0        0    
Toshiba        0      0        0          0         1       0        0        0        0    
Philips        0      0        0          0         0       1        0        0        0

score 1 · Accepted Answer · edited May 23 '17 at 12:13

You'll need to use a PIVOT to project your result in columns, although some manipulation is required to prepare the data beforehand, to generate your column names and group data per month:

SELECT ProdDesc, [Jan2013],[Feb2013],[Mar2013],[Apr2013],
                 [May2013],[Jun2013]
FROM 
(   
  SELECT ProdDesc, YearMonth, COUNT(P2PID) AS ProdCount
  FROM
  (
    SELECT p1.ProdDesc, 
      CAST(DATENAME(MONTH, [DATE]) AS VARCHAR(3)) + 
      DATENAME(YEAR, [DATE]) AS YearMonth, 
      p2.PID as P2PID
    FROM Product1 p1
    LEFT JOIN Product2 p2
    ON p1.PID = p2.PID
  ) x
  GROUP BY ProdDesc, YearMonth
) y
PIVOT
(
  SUM(ProdCount)
  for [YearMonth] IN ([Jan2013],[Feb2013],[Mar2013],
                      [Apr2013],[May2013],[Jun2013])
) pv;

SqlFiddle here

It is quite likely that you'll want to determine the columns (MonYYYY) dynamically. Have a look here on how to do this.

You can use ISNULL or COALESCE to replace NULLS in the output, if necessary ISNULL([Jan2013], 0) AS Jan2013

@Ram read up on the link posted - http://stackoverflow.com/a/11985946/314291. The STUFF bit is to generate the catenated string — StuartLC, May 13 '14 at 10:33

Need Monthly Report in sql server query

1 Answers1