Showing Month as Columns in SQL Server

Question

I have a Users table that has a CARD_EXPIRY column of datatype DateTime. Now, I want to show my data like this:

Jan | Feb | Mar | So on.....
--------------------
 2  |  3  |  5  |

The number shows number of card expired per month.

Here is what I tried:

DECLARE @Month TABLE
(
    Id INT,
    Name VARCHAR(10)
);

INSERT INTO @Month (Id, Name) 
VALUES (1, 'Jan'), (2, 'Feb'), (3, 'Mar'), (4, 'Apr'), (5, 'May'), 
       (6, 'Jun'), (7, 'Jul'), (8, 'Aug'), (9, 'Sep'), (10, 'Oct'), 
       (11, 'Nov'), (12, 'Dec');

SELECT COUNT('t'), M.Name 
FROM Users U 
INNER JOIN @Month M ON M.Id = DATEPART(MONTH, U.CARD_EXPIRY)
WHERE MyCondition 
GROUP BY M.Id, M.Name
ORDER BY M.Id

But the data is being returned row-wise, like this:

C       Name 
-------------
2764    Jan
3065    Feb
2849    mar
2158    Apr
2503    May
2925    Jun
2109    Jul
1399    Aug
1583    Sep
1820    Oct
1552    Nov
1912    Dec

As far as I understand you are looking for a PIVOT solution. https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/ — McNets, Jun 04 '17 at 19:23
If you need short name of month you can use CONVERT(VARCHAR(3), DATENAME(month, CARD_EXPIRY)) — Andrey Baulin, Jun 04 '17 at 19:27

score 2 · Accepted Answer · answered Jun 04 '17 at 19:33

You can use PIVOT, but you also can you group by, for axample:

 SELECT sum(case when DATEPART(MONTH, U.CARD_EXPIRY)=1 then 1 else 0) as Jan
       ,sum(case when DATEPART(MONTH, U.CARD_EXPIRY)=2 then 1 else 0) as Feb
       ,sum(case when DATEPART(MONTH, U.CARD_EXPIRY)=3 then 1 else 0) as mar
  ...................
       ,sum(case when DATEPART(MONTH, U.CARD_EXPIRY)=12 then 1 else 0) as Dec
 FROM Users U 
 GROUP BY M.Id, M.Name

Showing Month as Columns in SQL Server

1 Answers1