0

I need to show the count of all rows grouped by month and then pivot it Appreciate an easy solution for that

Or alternatively if someone can help me fix this

I've used dynamic pivot query

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

select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                      FROM OSCL
                      WHERE     ([status] = - 3) 

                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query =

'SELECT *
FROM
(
  SELECT
    callID,
    DATENAME(MONTH, CONVERT(DATETIME, createDate)) as Months
  FROM OSCL
  WHERE     ([status] = - 3) 
) AS t
PIVOT 
(
  COUNT(callID) 
  FOR Months IN( ' + @cols + ' )' +
' ) AS p ; ';

 execute(@query);

This gives me data like this

April | February | March
44    | 1        | 5

But, I want to show in Sequence

January | February | March | April (Till the current month)
0       | 1        | 5     | 44

Table Example Is This For Example

callID   |   U_Regn   |   createDate

What I Want To Show Is A Calendar

             January | February | March | April 
Database1 |   0      | 1        | 5     | 44
Database2 |   0      | 6        | 6     | 6
  • Create a table with all months than RIGHT JOIN it with OSCL so that all months will appear,than use COALESCE to make NULL into 0 – Mihai Apr 11 '14 at 12:29
  • 1
    Mihai is on the right track. Join your derived pivot table, p, to an derived table with 1 to 12 and the month name. Order by month_number – billinkc Apr 11 '14 at 12:49
1

Change

select @cols = STUFF((SELECT distinct ',' +
                    QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                  FROM OSCL
                  WHERE     ([status] = - 3) 

                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 
                    , 1, 1, '');

to

select @cols = STUFF((SELECT ',' +
                    QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                  FROM OSCL
                  WHERE     ([status] = - 3)
                  GROUP BY QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate))), DATEPART(MONTH, CONVERT(DATETIME, createDate))
                  ORDER BY DATEPART(MONTH, CONVERT(DATETIME, createDate))

                  FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 
                    , 1, 1, '');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.