How do I get distinct COUNT in pivot?

Question

I have a following table:

State   LAB GROUP   DATE    CODE    ID
UP  A   I   1-Jan   1   345
UP  R   S   1-Feb   1   456
UP  A   S   1-Jan   2   567
DL  R   S   1-Feb   3   678
DL  T   S   1-Jan   1   789
DL  A   S   1-Jan   2   900
MN  T   S   1-Jan   3   1011
MN  R   I   1-Feb   1   1122
MN  S   I   1-Feb   2   1233

I need a pivot table of following type:

STATE   A   R   T   TOTAL
UP  2   1   0   3
DL  1   1   1   3
MN  0   1   1   2

DISTINCT COUNT OF ID FOR EACH LAB FOR EACH STATE.

I then need the pivot tables filtered for following columns:

GROUP DATE CODE

So 1st table will have the pivot table above counting only those records which have GROUP=S 2nd table will have the pivot table above counting only those records which have CODE=1

and so on, I wish to put multiple conditions. and generate several tables one by one and export them.

If this is possible in SQL please let me know! I ruled out excel vba due to the size of table (source table will have 800,000 records approx).

Solution 1

Try this :-

Select [State],[A],[R],[T],Total = [A] + [R]+ [T]
from 
(
    Select [State],
           [A] = Sum(Case when LAB='A' then 1 else 0 END) ,
           [R] = Sum(Case when LAB='R' then 1 else 0 END) ,
           [T] = Sum(Case when LAB='T' then 1 else 0 END) 
   from YourTable
   group by [State]
)a

SQL FIDDLE

Solution 2

CREATE TABLE #t(States VARCHAR(10),LAB VARCHAR(5),GROUPs VARCHAR(5),DATEs VARCHAR(10),CODE INT,ID INT)
INSERT INTO #t values('UP','A','I','1-Jan',1,345)
INSERT INTO #t values('UP','R','S','1-Feb',1,456)
INSERT INTO #t values('UP','A','S','1-Jan',2,567)
INSERT INTO #t values('DL','R','S','1-Feb',3,678)
INSERT INTO #t values('DL','T','S','1-Jan',1,789)
INSERT INTO #t values('DL','A','S','1-Jan',2,900)
INSERT INTO #t values('MN','T','S','1-Jan',3,1011)
INSERT INTO #t values('MN','R','I','1-Feb',1,1122)
INSERT INTO #t values('MN','S','I','1-Feb',2,1233)



SELECT States,ISNULL(A,0) A,ISNULL(R,0) R,ISNULL(T,0) T,ISNULL(A,0)+ISNULL(R,0)+ISNULL(T,0) total
FROM 
(
    SELECT States,LAB,Count(ID) AS cnt FROM #t GROUP BY States,LAB /*apply GROUP DATE CODE condition here*/ 
) AS PVT
PIVOT(MAX(cnt) FOR LAB IN (A,R,T)) pvt

Solution 3

Another solution using PIVOT :

WITH PivotInUse AS (
	SELECT state,lab,COUNT(*) AS cnt
		FROM YourTable
		GROUP BY state,lab
		)
SELECT STATE
	,COALESCE([A], 0) AS A
	,COALESCE([R], 0) AS R
	,COALESCE([T], 0) AS T
	,COALESCE([A], 0) + COALESCE([R], 0) + COALESCE([T], 0) AS TOTAL
FROM PivotInUse
PIVOT(SUM(cnt) FOR lab IN ([A],[R],[T])) AS p;

Solution 4

Your sample table

SELECT * INTO #TEMP FROM
(               
    SELECT 'UP' [State],'A' LAB,'I' [GROUP],'1-Jan' [DATE],1 CODE,345 ID
    UNION ALL
    SELECT 'UP','R','S','1-Feb',1,456
    UNION ALL
    SELECT 'UP','A','S','1-Jan',2,567
    UNION ALL
    SELECT 'DL','R','S','1-Feb',3,678
    UNION ALL
    SELECT 'DL','T','S','1-Jan',1,789
    UNION ALL
    SELECT 'DL','A','S','1-Jan',2,900
    UNION ALL
    SELECT 'MN','T','S','1-Jan',3,1011
    UNION ALL
    SELECT 'MN','R','I','1-Feb',1,1122
    UNION ALL
    SELECT 'MN','S','I','1-Feb',2,1233  
)TAB

Now you need to get the distinct count of each state and get the sum as the result to show Total in pivoted result.

SELECT DISTINCT [State],LAB,SUM(CNT) CNT 
INTO #NEWTABLE
FROM 
(
    SELECT DISTINCT 
    [State],LAB,
    CASE WHEN [State] IS NULL THEN NULL ELSE COUNT([State]) OVER(PARTITION BY [State],LAB) END CNT  
    FROM #TEMP
)TAB
GROUP BY [State],LAB
WITH ROLLUP

Now we need to get the distinct columns for pivot(@cols) and columns to identify and replace null with zero in pivot(@NullToZeroCols)

DECLARE @cols NVARCHAR (MAX)
DECLARE @NullToZeroCols NVARCHAR (MAX)


SET @cols = SUBSTRING((SELECT DISTINCT ',['+LAB+']' FROM #NEWTABLE GROUP BY LAB  FOR XML PATH('')),2,8000)

SET @NullToZeroCols = SUBSTRING((SELECT DISTINCT ',ISNULL(['+LAB+'],0) AS ['+LAB+']' 
FROM #NEWTABLE GROUP BY LAB FOR XML PATH('')),2,8000)

Join the pivotted query with the #NEWTABLE to get the Total for each State

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.State,' + @NullToZeroCols + ',T2.CNT TOTAL FROM 
             (
                 SELECT DISTINCT [State],LAB,CNT FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 SUM(CNT)
                 FOR [LAB] IN (' + @cols + ')
            ) p
            JOIN #NEWTABLE T2 ON P.[STATE]=T2.[STATE] 
            WHERE P.State IS NOT NULL AND T2.LAB IS NULL AND T2.[STATE] IS NOT NULL;'

EXEC SP_EXECUTESQL @query

Here is your result

Here is the SQLFiddle http://sqlfiddle.com/#!3/c2588/1 (If it shows any error while loading the page, just click RUNSQL, it will work)

Now if you want to get the result as you said DISTINCT COUNT OF ID FOR EACH LAB FOR EACH STATE, just change

OVER(PARTITION BY [State],LAB)

to

OVER(PARTITION BY [State],LAB,Id)

which will show the following result after executing the pivot query

View more solutions