Pivot data using t sql

Question

I would really appreciate some help with a query I am having difficulty with. Here is an example of the data:

DEPT  FLAG   FLAG2  FLAG3

100    A             1
100    A      B  
201    A      1   
201    1      3
300           Z
301    5
301    2      3      FG

I need the following results: (distinct dept, with total count of flag, and flag2 that contains a value.) Using the data above, the results returned should look like:

dept  countFlag countFlag2 countFlag3

100      2         1            1
201      2         2            0
300      0         1            0
301      2         1            1

100 2, 1, 1 <--- for dept 100, there are two rows that contain a value in flag column and 1 that contains a value in flag2 and 1 that contains a value in flag 3.

I hope this makes sense. Thanks in advance.

Answer 1

You donot need pivot for this. Count(Distinct) is sufficient

Select Dept, 
       Count(distinct Flag) as countFlag, 
       Count(distinct Flag2) as CountFlag2, 
       Count(distinct Flag3) as CountFlag3
from dept 
group by Dept

To include all non-null fields, use following

Select Dept, 
       sum(case flag when null then 0 else 1) as countFlag, 
       sum(case flag2 when null then 0 else 1)  as CountFlag2, 
       sum(case flag3 when null then 0 else 1)  as CountFlag3
from dept 
group by Dept

Answer 2

Test Data

CREATE TABLE Table_Name (DEPT INT,  
                        FLAG VARCHAR(2) 
                        ,FLAG2 VARCHAR(2),
                        FLAG3 VARCHAR(2))

INSERT INTO Table_Name(DEPT,  FLAG,   FLAG2,  FLAG3)
VALUES
(100,'A',null,'1'),
(100,'A','B', null),  
(201,'A','1', null),   
(201,'1','3', null),
(300, null,'Z',null),
(301,'5',null,null),
(301,'2','3','FG')

Query

SELECT DISTINCT DEPT, COUNT(FLAG) AS FLAG, 
                COUNT(FLAG2) AS FLAG2, COUNT(FLAG3) AS FLAG3
FROM Table_Name
GROUP BY DEPT

Result Set

DEPT    FLAG    FLAG2   FLAG3
100     2       1       1
201     2       2       0
300     0       1       0
301     2       1       1