Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot Expand / Collapse
Author
Message
Posted Tuesday, July 06, 2010 8:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:13 PM
Points: 190, Visits: 637
Hello,
I am trying to count distinct employees by month for each department. Below is my source table.
IF OBJECT_ID('TempDB..#Table') IS NOT NULL
DROP TABLE #Table
CREATE TABLE #Table (Department VARCHAR(20), EmpID INT, [Month] INT)
INSERT INTO #Table
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,6 UNION ALL
SELECT 'Marketing',268638,6



The result set should look like this.
IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (Department VARCHAR(20),[1] INT,[2] INT, [3] INT,[4] INT,[5] INT,[6] INT,[7] INT,[8] INT,[9] INT,[10] INT,[11] INT,[12] INT)
INSERT INTO #Results
SELECT 'Marketing',0,0,1,0,0,1,0,0,0,0,0,0

There is the same employee 268638 twice in the month of 3(March) and 6(June). I need to pivot the result set with distinct counts by month.
Any help please! Thanks.
Post #947942
Posted Tuesday, July 06, 2010 8:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 4,942, Visits: 8,864
You can pre-aggregate the results in the pivot source:

IF OBJECT_ID('TempDB..#Table') IS NOT NULL
DROP TABLE #Table
CREATE TABLE #Table (Department VARCHAR(20), EmpID INT, [Month] INT)
INSERT INTO #Table
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,3 UNION ALL
SELECT 'Marketing',268638,6 UNION ALL
SELECT 'Marketing',268638,6





IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results (Department VARCHAR(20), [1] INT,[2] INT, [3] INT,[4] INT,[5] INT,[6] INT,[7] INT,[8] INT,[9] INT,[10] INT,[11] INT,[12] INT)



INSERT INTO #Results
SELECT Department,
ISNULL([1],0),
ISNULL([2],0),
ISNULL([3],0),
ISNULL([4],0),
ISNULL([5],0),
ISNULL([6],0),
ISNULL([7],0),
ISNULL([8],0),
ISNULL([9],0),
ISNULL([10],0),
ISNULL([11],0),
ISNULL([12],0)
FROM (
SELECT Department, Month, CNT = COUNT(DISTINCT EmpId)
FROM #Table
GROUP BY Department, Month
) AS SRC
PIVOT (SUM(CNT) FOR Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT


SELECT * FROM #Results

Does it do the trick for you?


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #947962
Posted Tuesday, July 06, 2010 9:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:13 PM
Points: 190, Visits: 637
Perfect. Thank you so much.
Post #947972
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse