CODEWITHSUNDEEP
sql-serversql-server-2008-r2pivot-table
MAK
MAK

Reputation: 7260

SQL Server 2008 R2: Pivot table with count

I have the following table to pivot.

Table:

CREATE TABLE Emp_Month
(
    EMPID int,
    [Month] varchar(10)
);

Insertion:

INSERT INTO Emp_Month VALUES(101,'July');
INSERT INTO Emp_Month VALUES(102,'June');
INSERT INTO Emp_Month VALUES(103,'May');
INSERT INTO Emp_Month VALUES(104,'April');
INSERT INTO Emp_Month VALUES(105,'March');

INSERT INTO Emp_Month VALUES(201,'July');
INSERT INTO Emp_Month VALUES(202,'July');
INSERT INTO Emp_Month VALUES(203,'June');
INSERT INTO Emp_Month VALUES(204,'July');
INSERT INTO Emp_Month VALUES(205,'June');

INSERT INTO Emp_Month VALUES(301,'January');
INSERT INTO Emp_Month VALUES(302,'January');
INSERT INTO Emp_Month VALUES(303,'February');
INSERT INTO Emp_Month VALUES(304,'February');
INSERT INTO Emp_Month VALUES(305,'February');

Expected Output: I want to print the COUNT(last_months_from_current_date)

Last_6_Month    Last_5_Month    Last_4_Month    Last_3_Month    Last_2_Month    Last_1_Month
--------------------------------------------------------------------------------------------
13              10              9               8               7               4

My try:

select *
from 
(
    SELECT t.Month,t.[Month] as Mon
    FROM Emp_Month t
) src
pivot
(
  COUNT(Mon)
  for [Month] in ([Last_6_Month],[Last_5_Month],[Last_4_Month],[Last_3_Month],[Last_2_Month],[Last_1_Month])
) piv;

But getting all zero values.

Upvotes: 0

Views: 109

Answers (4)

Ryan B.
Ryan B.

Reputation: 3665

Well, this twisted my head a bit. But this will do what you're asking.

The thing you are missing is that to do a running total, you have to have some way to order your set. So there's construction here for getting a month number. It's not the best but it is working for the example.

After grouping and doing the running total, a pivot is a bit over the top, but you were asking for one and so here you go. Hope it helps.

(*I made some changes to the accepted answer to make the column headers of the pivot self-updating. Otherwise, you would have needed to modify this every month)

;WITH 
    MonthsCountedOrdered AS
    (
        SELECT count(EMPID) empCount, Month, Datediff(M,  [Month] + ' 1, 2017', getdate()) + 1 monthsPast 
        FROM Emp_Month 
        GROUP BY [Month]
    )
    , RunningTotal AS
    (
        SELECT 
            Month
            , SUM(empCount) OVER (Order By monthsPast ROWS UNBOUNDED PRECEDING) tot
            , 'Last_' + cast(monthsPast as varchar(2)) + '_Month' as ColumnHeader 
        FROM
            MonthsCountedOrdered
        WHERE
            monthsPast <= 6
    )

    SELECT *
    FROM 
    (
        SELECT t.ColumnHeader,t.tot
        FROM RunningTotal t
    ) src
    pivot
    (
      Sum(tot)
      for [ColumnHeader] in (Last_6_Month,Last_5_Month,Last_4_Month,Last_3_Month,Last_2_Month,Last_1_Month)
    ) piv;

Result:

Last_6_Month Last_5_Month Last_4_Month Last_3_Month Last_2_Month Last_1_Month
------------ ------------ ------------ ------------ ------------ ------------
13           10           9            8            7            4

Upvotes: 1

user7715598
user7715598

Reputation:

By dynamic sql approach i did this,This might be your required code

DECLARE @DynamicCol NVARCHAR(max), 
        @Sql        NVARCHAR(max) 

SELECT @DynamicCol = Stuff((SELECT DISTINCT ', ' + Quotename([month]) 
                            FROM   #emp_month 
                            FOR xml path ('')), 1, 1, '') 

SET @Sql=N'SELECT  ' + @DynamicCol  + 'FROM  
            (
         SELECT t.[MONTH],COUNT(t.[Month]) AS Mon     
         FROM #Emp_Month t GROUP BY [MONTH] 
         )AS SRC 
         PIVOT 
         ( 
         MAX([Mon]) FOR [Month] IN (' + @DynamicCol + ') 
         ) AS PVT ' 

PRINT @Sql 

EXEC (@Sql)

You can set the below condtion also

SET @Sql=N'SELECT  ' + @DynamicCol  + 'FROM  
            (
         SELECT t.[MONTH],t.[Month] AS Mon     
         FROM #Emp_Month t 
         )AS SRC 
         PIVOT 
         ( 
         COUNT([Mon]) FOR [Month] IN (' + @DynamicCol + ') 
         ) AS PVT '

Upvotes: 0

wrslphil
wrslphil

Reputation: 258

I would first of all be counting the EMPID field rather than the Month, though both will work in this case.

Also you have nothing for it to pivot as none of the data in the Month Column matches your column list within your pivot. If you want to look at pivoting on last 6 months, then you'll have to add a new column or create a view or sub-query to add a column with values showing [Last_6_Month]

If you just want to count the months then you can always just use

    SELECT *
FROM 
(
    SELECT t.Month,EMPID
    FROM Emp_Month t
) src
PIVOT
(
  COUNT(EMPID)
  FOR [Month] in (
  [January],[February],[March],[April],[May],[June],[July],
  [August],[September],[October],[November],[December]
  )
) piv;

Upvotes: 0

Eli
Eli

Reputation: 2608

@MAK here's your query that you tried: 

select *
from 
(
    SELECT t.Month,t.[Month] as Mon
    FROM Emp_Month t
) src
pivot
(
  COUNT(Mon)
   for [Month] in ([Last_6_Month],[Last_5_Month],[Last_4_Month],[Last_3_Month],[Last_2_Month],[Last_1_Month])

) piv;

The Following line in your query needs to have actual values in the MON column. From your sample, it seems as if the actual month names are in there, though you're putting in your own values, hence the number will be 0. Try putting in the month names which you actually have.

for [Month] in ([Last_6_Month],[Last_5_Month],[Last_4_Month],[Last_3_Month],[Last_2_Month],[Last_1_Month])

something like:

for [Month] in ([June],[July],[April])

Upvotes: 1

Related Questions