How to unpivot columns and GROUP BY based on values in SQL Server?

Question

I want to calculate distinct PID and VID counts per year.

Condition:

1. Separate count for column A or B or C or D  having value 1  ( A_to_D) 
2. Separate count for column E  having value 1  (E)
3. Separate count for column F  having value 1   (F)
4. Separate count for ALL A to F column are NULL  (ALL_NULL)

In the output I want a separate new column Alpha with the following values:

A_to_D, E, F and ALL_Null

Current output of table:

PID VID Flag    Date        A       B       C       D       E       F
1   A1  0       10/17/2013  NULL    NULL    NULL    NULL    NULL    NULL
2   A2  1       5/27/2014   1       NULL    NULL    1       NULL    NULL
3   A3  NULL    2/23/2015   NULL    NULL    NULL    NULL    1       NULL
4   A4  NULL    12/6/2013   NULL    0       NULL    NULL    NULL    NULL
5   A5  NULL    7/14/2016   NULL    NULL    NULL    NULL    NULL    1
6   A6  NULL    4/29/2015   NULL    1       1       NULL    NULL    NULL
7   A7  1       9/30/2016   1       NULL    NULL    NULL    NULL    NULL
8   A8  NULL    6/28/2016   NULL    NULL    NULL    NULL    NULL    NULL
9   A9  1       11/20/2013  NULL    NULL    NULL    NULL    NULL    NULL
10  A10 2       10/8/2015   NULL    1       NULL    NULL    NULL    NULL

Here:

select datepart(Year,date) ,Count(distinct PID) ,Count( distinct VID)
from table
where A is not null or B is Not NUll or C is not null 
   or D is not null or E is not Null or F is not null
group by datepart(Year,date)

Expected output:

Year        Count_PID       Count_VID       Alpha    
2013                                        A_to_D
2013                                        E
2013                                        F
2013            2               2           ALL_NULL
2014            1               1           A_to_D
2014                                        E
2014                                        F
2014                                        ALL_NULL
2015            2               2           A_to_D
2015            1               1           E
2015                                        F
2015                                        ALL_NULL
2016            2               2           A_to_D
2016                                        E
2016            1                           F   
2016            1                           ALL_NULL

Why the Count_VID(2016) is blank for 'F' and 'ALL_NULL'? also why the Count_VID/PID(2016) is 2 for 'A_to_D'? — Abdul Rasheed, Nov 14 '17 at 11:56
yeah so what is the deal with the flag? When is it supposed to mean count VID and when is it NOT supposed to count. Your expected output is not consistent between the years — Matt, Nov 16 '17 at 05:38

Paul Maxwell · Answer 1 · 2017-11-13T11:30:16.423

I chose to use a common table expression (cte) to hold the basic counts which are formed using case expressions for each of 8 different conditions (4 for PIDs and 4 for VIDs). The cte is then used as a source of the year dimension needed for the final result which gets cross joined to the list of 4 alpha labels. Then the cte is used again (twice) - unpivoted - to enable left joining the counts into the requested final row structure. Nulls in that results are deliberate, but could be replaced with empty string if required by using coalesce() or isnull() in the final select clause. Note I prefer to "unpivot" using cross apply and values as it allows an almost WYSIWYG layout of the rows that get produced as it at least a equally efficient as the unpivot command (ref below).

Demo at: SQL Fiddle

CREATE TABLE Table1
    ([PID] int, [VID] varchar(3), [Flag] varchar(4), [Date] datetime, [A] varchar(4), [B] varchar(4), [C] varchar(4), [D] varchar(4), [E] varchar(4), [F] varchar(4))
;

INSERT INTO Table1
    ([PID], [VID], [Flag], [Date], [A], [B], [C], [D], [E], [F])
VALUES
    (1, 'A1', '0', '2013-10-17 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL),
    (2, 'A2', '1', '2014-05-27 00:00:00', '1', NULL, NULL, '1', NULL, NULL),
    (3, 'A3', NULL, '2015-02-23 00:00:00', NULL, NULL, NULL, NULL, '1', NULL),
    (4, 'A4', NULL, '2013-12-06 00:00:00', NULL, '0', NULL, NULL, NULL, NULL),
    (5, 'A5', NULL, '2016-07-14 00:00:00', NULL, NULL, NULL, NULL, NULL, '1'),
    (6, 'A6', NULL, '2015-04-29 00:00:00', NULL, '1', '1', NULL, NULL, NULL),
    (7, 'A7', '1', '2016-09-30 00:00:00', '1', NULL, NULL, NULL, NULL, NULL),
    (8, 'A8', NULL, '2016-06-28 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL),
    (9, 'A9', '1', '2013-11-20 00:00:00', NULL, NULL, NULL, NULL, NULL, NULL),
    (10, 'A10', '2', '2015-10-08 00:00:00', NULL, '1', NULL, NULL, NULL, NULL)
;

Proposed Query:

/* common table expression used so the results may be reused */
with cte as (
      select
            year([date]) [Year]
          , count(distinct pA_to_D) pA_to_D
          , count(distinct pE) pE
          , count(distinct pF) pF
          , count(distinct pALL_NULL) pALL_NULL
          , count(distinct vA_to_D) vA_to_D
          , count(distinct vE) vE
          , count(distinct vF) vF
          , count(distinct vALL_NULL) vALL_NULL
      from (
            select
                  pid, vid, flag, [date]
                , case when a = 1 or b = 1 or c = 1 or d = 1 then pid end pA_to_D
                , case when E = 1 then pid end pE
                , case when F = 1 then pid end pF
                , case when coalesce(a,b,c,d,e,f) IS NULL then pid end pALL_NULL
                , case when flag is not null and a = 1 or b = 1 or c = 1 or d = 1 then vid end vA_to_D
                , case when flag is not null and E = 1 then vid end vE
                , case when flag is not null and F = 1 then vid end vF
                , case when flag is not null and coalesce(a,b,c,d,e,f) IS NULL then vid end vALL_NULL
            from Table1
            ) t
      group by 
           year([date])
  )
select
       y.[Year], p.count_pid, v.count_vid, a.alpha
from (select distinct [Year] from cte) y
cross join (
        select 'A_to_D' as Alpha union all
        select 'E'               union all
        select 'F'               union all
        select 'ALL_NULL'
        ) a
left join (
      select cte.Year, ca.alpha, ca.count_pid
      from cte
      cross apply (
          values
                ('A_to_D'  ,pA_to_D)
              , ('E'       ,pE)
              , ('F'       ,pF)
              , ('ALL_NULL',pALL_NULL)
            ) ca (alpha, count_pid)
       where ca.count_pid > 0
       ) p on y.[Year] = p.[Year] and a.alpha = p.alpha
left join (
      select cte.Year, ca.alpha, ca.count_vid
      from cte
      cross apply (
          values
                ('A_to_D'  ,vA_to_D)
              , ('E'       ,vE)
              , ('F'       ,vF)
              , ('ALL_NULL',vALL_NULL)
            ) ca (alpha, count_vid)
       where ca.count_vid > 0
       ) v on y.[Year] = v.[Year] and a.alpha = v.alpha
;

Results:

| Year | count_pid | count_vid |    alpha |
|------|-----------|-----------|----------|
| 2013 |    (null) |    (null) |   A_to_D |
| 2013 |    (null) |    (null) |        E |
| 2013 |    (null) |    (null) |        F |
| 2013 |         2 |         2 | ALL_NULL |
| 2014 |         1 |         1 |   A_to_D |
| 2014 |    (null) |    (null) |        E |
| 2014 |    (null) |    (null) |        F |
| 2014 |    (null) |    (null) | ALL_NULL |
| 2015 |         2 |         2 |   A_to_D |
| 2015 |         1 |    (null) |        E |
| 2015 |    (null) |    (null) |        F |
| 2015 |    (null) |    (null) | ALL_NULL |
| 2016 |         1 |         1 |   A_to_D |
| 2016 |    (null) |    (null) |        E |
| 2016 |         1 |    (null) |        F |
| 2016 |         1 |    (null) | ALL_NULL |

For details on using CROSS APPLY and VALUES to UNPIVOT, see Spotlight on UNPIVOT, Part 1 by Brad Schultz

Innermost Query:

It can be useful to see the initial results to help trace following actions. This is he innermost subquery within the cte as a separate query with the results below:

/* initial results, prior to unpivot */
           select
                  pid, vid, flag, [date]
                , case when a = 1 or b = 1 or c = 1 or d = 1 then pid end pA_to_D
                , case when E = 1 then pid end pE
                , case when F = 1 then pid end pF
                , case when coalesce(a,b,c,d,e,f) IS NULL then pid end pALL_NULL
                , case when flag is not null and a = 1 or b = 1 or c = 1 or d = 1 then vid end vA_to_D
                , case when flag is not null and E = 1 then vid end vE
                , case when flag is not null and F = 1 then vid end vF
                , case when flag is not null and coalesce(a,b,c,d,e,f) IS NULL then vid end vALL_NULL
            from Table1
            order by [date]
;

Results:

| pid | vid |   flag |                 date | pA_to_D |     pE |     pF | pALL_NULL | vA_to_D |     vE |     vF | vALL_NULL |
|-----|-----|--------|----------------------|---------|--------|--------|-----------|---------|--------|--------|-----------|
|   1 |  A1 |      0 | 2013-10-17T00:00:00Z |  (null) | (null) | (null) |         1 |  (null) | (null) | (null) |        A1 |
|   9 |  A9 |      1 | 2013-11-20T00:00:00Z |  (null) | (null) | (null) |         9 |  (null) | (null) | (null) |        A9 |
|   4 |  A4 | (null) | 2013-12-06T00:00:00Z |  (null) | (null) | (null) |    (null) |  (null) | (null) | (null) |    (null) |
|   2 |  A2 |      1 | 2014-05-27T00:00:00Z |       2 | (null) | (null) |    (null) |      A2 | (null) | (null) |    (null) |
|   3 |  A3 | (null) | 2015-02-23T00:00:00Z |  (null) |      3 | (null) |    (null) |  (null) | (null) | (null) |    (null) |
|   6 |  A6 | (null) | 2015-04-29T00:00:00Z |       6 | (null) | (null) |    (null) |      A6 | (null) | (null) |    (null) |
|  10 | A10 |      2 | 2015-10-08T00:00:00Z |      10 | (null) | (null) |    (null) |     A10 | (null) | (null) |    (null) |
|   8 |  A8 | (null) | 2016-06-28T00:00:00Z |  (null) | (null) | (null) |         8 |  (null) | (null) | (null) |    (null) |
|   5 |  A5 | (null) | 2016-07-14T00:00:00Z |  (null) | (null) |      5 |    (null) |  (null) | (null) | (null) |    (null) |
|   7 |  A7 |      1 | 2016-09-30T00:00:00Z |       7 | (null) | (null) |    (null) |      A7 | (null) | (null) |    (null) |

Bijan Ghasemi · Answer 2 · 2017-11-09T14:44:39.713

SELECT Part1Q.YearCol,Part1Q.Count_PID,Part2Q.Count_VID,Part1Q.Col
FROM
(
    SELECT 
        YearCol,
        SUM(CASE Col 
                WHEN 'A_to_D' THEN 
                    CASE WHEN TQ.A>0 OR TQ.B>0 OR TQ.C>0 OR TQ.D>0 THEN 1 ELSE 0 END
                WHEN 'E' THEN 
                    CASE WHEN TQ.E>0 THEN 1 ELSE 0 END
                WHEN 'F' THEN 
                    CASE WHEN TQ.F>0 THEN 1 ELSE 0 END
                WHEN 'ALL_NULL' THEN 
                    CASE WHEN TQ.A IS NULL AND TQ.B IS NULL AND TQ.C IS NULL AND TQ.D IS NULL AND TQ.E IS NULL AND  TQ.F IS NULL THEN 1 ELSE 0 END
            END
            ) AS Count_PID, 
        AlphaQ.Col

    FROM 
    (
        SELECT 
            DATEPART(YEAR,DataTable.Date) AS YearCol,PID,Flag,Date,A,B,C,D,E,F
        FROM DataTable
        GROUP BY DATEPART(YEAR,DataTable.Date),PID,Flag,Date,A,B,C,D,E,F
    )
     AS TQ
    LEFT JOIN 
    (
        SELECT 'A_to_D' AS Col
        UNION
        SELECT 'E' AS Col
        UNION
        SELECT 'F' AS Col
        UNION
        SELECT 'ALL_NULL' AS Col
    )AlphaQ ON 1=1
    GROUP BY YearCol,AlphaQ.Col
) AS Part1Q
LEFT JOIN 
(   
    SELECT 
        YearCol,
        SUM(CASE Col 
                WHEN 'A_to_D' THEN 
                    CASE WHEN TQ.A>0 OR TQ.B>0 OR TQ.C>0 OR TQ.D>0 THEN 1 ELSE 0 END
                WHEN 'E' THEN 
                    CASE WHEN TQ.E>0 THEN 1 ELSE 0 END
                WHEN 'F' THEN 
                    CASE WHEN TQ.F>0 THEN 1 ELSE 0 END
                WHEN 'ALL_NULL' THEN 
                    CASE WHEN TQ.A IS NULL AND TQ.B IS NULL AND TQ.C IS NULL AND TQ.D IS NULL AND TQ.E IS NULL AND  TQ.F IS NULL THEN 1 ELSE 0 END
            END
            ) AS Count_VID, 
        AlphaQ.Col

    FROM 
    (
        SELECT 
            DATEPART(YEAR,DataTable.Date) AS YearCol,VID,Flag,Date,A,B,C,D,E,F
        FROM DataTable
        GROUP BY DATEPART(YEAR,DataTable.Date),VID,Flag,Date,A,B,C,D,E,F
    )
     AS TQ
    LEFT JOIN 
    (
        SELECT 'A_to_D' AS Col
        UNION
        SELECT 'E' AS Col
        UNION
        SELECT 'F' AS Col
        UNION
        SELECT 'ALL_NULL' AS Col
    )AlphaQ ON 1=1
    GROUP BY YearCol,AlphaQ.Col
)AS Part2Q ON Part2Q.YearCol = Part1Q.YearCol AND Part2Q.Col = Part1Q.Col
ORDER BY Part1Q.YearCol,Part1Q.Col

Its not calculate count of distinct PID or vid. if we insert first record two time then result changed — rohit patil, Nov 09 '17 at 12:21

score 0 · Answer 3 · answered Nov 13 '17 at 17:48

I have followed the simple steps below to get to the result

1) pid_prep - Get the Year, PID and the Counts 2) vid_prep - Get the Year, VID and the Counts 3) pid_pivoted - Pivot the counts for PID 4) vid_pivoted - Pivot the counts for VID 5) main SELECT - Join pid_pivoted and vid_pivoted to get the result.

WITH pid_prep
AS
    (SELECT DISTINCT [PID], YEAR([Date]) AS Year,
           CASE WHEN [A] =1 OR [B] = 1 OR [C] = 1 OR [D] = 1 THEN 1 ELSE 0  END AS AToD,
           CASE WHEN [E] =1 THEN 1  ELSE 0 END AS E,
           CASE WHEN [F] =1 THEN 1  ELSE 0 END AS F,
           CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN 1 ELSE 0 END AS AllNull
      FROM @Table1),
vid_prep
AS
    (SELECT DISTINCT [VID], YEAR([Date]) AS Year,
           CASE WHEN [A] =1 OR [B] = 1 OR [C] = 1 OR [D] = 1 THEN 1 ELSE 0  END AS AToD,
           CASE WHEN [E] =1 THEN 1  ELSE 0 END AS E,
           CASE WHEN [F] =1 THEN 1  ELSE 0 END AS F,
           CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN 1 ELSE 0 END AS AllNull
      FROM @Table1),
pid_pivoted 
AS
    (SELECT Year, cols, SUM(counts) AS counts
       FROM pid_prep 
     UNPIVOT (counts for cols in (AToD, E, F, AllNull)) u
     GROUP BY Year, cols),
vid_pivoted 
AS
    (SELECT Year, cols, SUM(counts) AS counts
       FROM vid_prep 
     UNPIVOT (counts for cols in (AToD, E, F, AllNull)) v
      GROUP BY Year, cols)
SELECT pp.Year, NULLIF(pp.counts, 0) AS PID_counts, NULLIF(vp.counts, 0) AS VID_Counts, pp.cols
  FROM pid_pivoted pp LEFT JOIN 
       vid_pivoted vp ON (pp.Year = vp.Year AND pp.cols = vp.cols)
ORDER BY pp.Year, pp.cols ;

Output is as below. Let me know if you want anything explained.

KumarHarsh · Answer 4 · 2017-11-16T06:01:32.650

I am using your sample data.Your output is wrong in few case. Try my script using various sample data.

create table #tbl(PID int,VID varchar(30),Flag int,Dates Date
,A int,B int,C int,D int,E int,F int,Years as year(dates))

insert into #tbl VALUES
(1   ,'A1',  0       ,'10/17/2013',  NULL  ,NULL ,NULL, NULL,NULL,NULL)
,(2   ,'A2',  1       ,'5/27/2014',  1     ,NULL ,NULL, 1   ,NULL,NULL)
,(3   ,'A3',  NULL    ,'2/23/2015',  NULL  ,NULL ,NULL, NULL,1   ,NULL)
,(4   ,'A4',  NULL    ,'12/6/2013',  NULL  ,0    ,NULL, NULL,NULL,NULL)
,(5   ,'A5',  NULL    ,'7/14/2016',  NULL  ,NULL ,NULL, NULL,NULL,1   )
,(6   ,'A6',  NULL    ,'4/29/2015',  NULL  ,1    ,1   , NULL,NULL,NULL)
,(7   ,'A7',  1       ,'9/30/2016',  1     ,NULL ,NULL, NULL,NULL,NULL)
,(8   ,'A8',  NULL    ,'6/28/2016',  NULL  ,NULL ,NULL, NULL,NULL,NULL)
,(9   ,'A9',  1       ,'11/20/2013',  NULL  ,NULL ,NULL, NULL,NULL,NULL)
,(10  ,'A10', 2       ,'10/8/2015',  NULL  ,1    ,NULL, NULL,NULL,NULL)


-- This is hard coded
create table #temp (Alpha varchar(50))
insert into #temp VALUES ('A_to_D'),('E'),('F'),('ALL_NULL')

-- you can decide the value of @StartYear and @Maxyear according to some logic
-- don't consider it hard coded .It is no big deal
declare @StartYear int=2013
declare @Maxyear int =4

create table #year (Years int)
insert into #year 
select TOP(@Maxyear) @StartYear+(ROW_NUMBER()OVER(ORDER BY number)-1) 
from master..spt_values 
--select * from #year

;

WITH CTE
AS (
    SELECT *
    FROM #year
        ,#temp
    )
    ,CTE1
AS (
    SELECT  t.Years
        ,sum(CASE 
                WHEN A = 1
                    OR B = 1
                    OR c = 1
                    OR d = 1
                    THEN 1
                END) PA_to_D
        ,sum(CASE 
                WHEN (flag IS NOT NULL)
                    AND (
                        A = 1
                        OR B = 1
                        OR c = 1
                        OR d = 1
                        )
                    THEN 1
                END) VA_to_D
        ,sum(CASE 
                WHEN E = 1
                    THEN 1
                END) P_E
        ,sum(CASE 
                WHEN (flag IS NOT NULL)
                    AND (E = 1)
                    THEN 1
                END) V_E
        ,sum(CASE 
                WHEN F = 1
                    THEN 1
                END) P_F
        ,sum(CASE 
                WHEN (flag IS NOT NULL)
                    AND (F = 1)
                    THEN 1
                END) V_F
        ,sum(CASE 
                WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL
                    THEN 1
                END) P_ALL_NULL
        ,sum(CASE 
                WHEN (flag IS NOT NULL)
                    AND COALESCE([A], [B], [C], [D], [E], [F]) IS NULL
                    THEN 1
                END) V_ALL_NULL
    FROM #tbl t
    where exists(select years from #year Y where Y.years=t.years)
    GROUP BY t.Years
    )

SELECT c.years
    ,c.alpha
    ,ca.Count_PID
    ,ca.Count_VID
FROM cte c
CROSS APPLY (
    SELECT CASE 
            WHEN c.years = t.years
                AND alpha = 'A_to_D'
                THEN PA_to_D
            WHEN c.years = t.years
                AND alpha = 'E'
                THEN P_E
            WHEN c.years = t.years
                AND alpha = 'F'
                THEN P_F
            WHEN c.years = t.years
                AND alpha = 'ALL_NULL'
                THEN P_ALL_NULL
            ELSE NULL
            END Count_PID
        ,CASE 
            WHEN c.years = t.years
                AND alpha = 'A_to_D'
                THEN VA_to_D
            WHEN c.years = t.years
                AND alpha = 'E'
                THEN V_E
            WHEN c.years = t.years
                AND alpha = 'F'
                THEN V_F
            WHEN c.years = t.years
                AND alpha = 'ALL_NULL'
                THEN V_ALL_NULL
            ELSE NULL
            END Count_VID
    FROM CTE1 t
    WHERE years = c.years
    ) ca
ORDER BY c.Years


drop table #tbl
drop table #year
drop table #temp

Explanation

: I have use Years as year(dates) as computed column (or may be permanent column) to take advantage of index in future.It is just and idea.

If you use Year(Dates) instead of Years then also output will be OK

so your #years table is off because it will put years 2014 to 2017 into it not 2013 to 2016 the issue is the addition of the ROW_NUMBER() you need to subtract 1 to make it 0 based. — Matt, Nov 16 '17 at 05:42
I am glad you corrected it, it is an easy mistake and I just wanted to help you identify it to get it corrected. I assume you meant NOW not NO because you didn't correct until after my comment but either way it improves your answer which was my goal cheers — Matt, Nov 16 '17 at 05:50

score 0 · Answer 5 · answered Nov 16 '17 at 05:47

To be honest I am a little lost on the flag because it doesn't appear that you are treating it the same from year to year when identifying your DISTINCT count of VID. so if you can elaborate I can easily help you identify where to use it as criteria to filter out the VID appropriately. But this might get you close. Basic steps find all years and alphas create a cross join and determine if the table row for that year meets the criteria if it does count it.

Test Data:

DECLARE @Table AS TABLE (PID INT, VID VARCHAR(3), Flag INT, [Date] DATE, A INT, B INT, C INT, D INT, E INT, F INT)

INSERT INTO @Table VALUES
(1 ,'A1',0   ,'10/17/2013',NULL,NULL,NULL,NULL,NULL,NULL)
,(2 ,'A2',1   ,'5/27/2014',1   ,NULL,NULL,1   ,NULL,NULL)
,(3 ,'A3',NULL,'2/23/2015',NULL,NULL,NULL,NULL,1   ,NULL)
,(4 ,'A4',NULL,'12/6/2013',NULL,0   ,NULL,NULL,NULL,NULL)
,(5 ,'A5',NULL,'7/14/2016',NULL,NULL,NULL,NULL,NULL,1     )
,(6 ,'A6',NULL,'4/29/2015',NULL,1   ,1   ,NULL,NULL,NULL)
,(7 ,'A7',1   ,'9/30/2016',1   ,NULL,NULL,NULL,NULL,NULL)
,(8 ,'A8',NULL,'6/28/2016',NULL,NULL,NULL,NULL,NULL,NULL)
,(9 ,'A9',1   ,'11/20/2013',NULL,NULL,NULL,NULL,NULL,NULL)
,(10,'A10',2   ,'10/8/2015',NULL,1   ,NULL,NULL,NULL,NULL)

Query:

;WITH cteYears AS (
    SELECT DISTINCT [Year] = YEAR([Date])
    FROM
       @Table
)
, cteAlphas AS (
    SELECT Alpha
    FROM
       (VALUES ('A_to_D'),('E'),('F'),('ALL_NULL')) t(Alpha)
)

, cteMeetsCriteria AS (
    SELECT
       y.[Year]
       ,t.PID
       ,t.VID
       ,t.Flag
       ,a.Alpha
       ,MeetsCriteria = CASE
          WHEN a.Alpha = 'A_to_D' AND (t.A = 1 OR t.B = 1 OR t.C = 1 OR t.D = 1) THEN 1
          WHEN a.Alpha = 'E' AND t.E = 1 THEN 1
          WHEN a.Alpha = 'F' AND t.F = 1 THEN 1
          WHEN a.Alpha = 'ALL_NULL' AND COALESCE(t.A,t.B,t.C,t.D,t.E,t.F) IS NULL THEN 1
          ELSE 0
       END
    FROM
       cteYears y
       CROSS JOIN cteAlphas a
       LEFT JOIN @Table t
       ON y.[Year] = YEAR(t.[Date])
)

SELECT
    [Year]
    ,Count_PID = NULLIF(COUNT(DISTINCT IIF(MeetsCriteria = 1, PID, NULL)),0)
    ,Count_VID = NULLIF(COUNT(DISTINCT IIF(MeetsCriteria = 1 AND Flag IS NOT NULL, VID, NULL)),0)
    ,Alpha
FROM
    cteMeetsCriteria
GROUP BY
    [Year]
    ,Alpha
ORDER BY
    [Year]
    ,CASE Alpha WHEN 'A_to_D' THEN 1 WHEN 'E' THEN 2 WHEN 'F' THEN 3 WHEN 'ALL_NULL' THEN 4 ELSE 5 END

note if a row meets more than 1 criteria it will be counted more than once in the results!

Link showing how it works: http://rextester.com/ITVT9711

If is very easy to move the Flag to different locations to filter or change up the cteMeetsCriteria to be able to treat the flag differently depending on what the alpha is.

score 0 · Answer 6 · answered Nov 16 '17 at 12:05

You can reach this output using a simple UNION query,

SELECT  YEAR([Date])    AS  [Year]
    ,NULLIF(COUNT(CASE WHEN A = 1 OR B = 1 OR C = 1 OR D = 1 THEN [PID] END),0) AS [Count_PID]
    ,NULLIF(COUNT(CASE WHEN A = 1 OR B = 1 OR C = 1 OR D = 1 THEN [VID] END),0) AS [Count_VID]
    ,'A_to_D'       AS  [alpha] 
    ,1              AS  [sl_no]
FROM    @Table1
GROUP BY YEAR([Date])
UNION ALL
SELECT  YEAR([Date])
    ,NULLIF(COUNT(CASE WHEN E = 1 THEN [PID] END),0)
    ,NULLIF(COUNT(CASE WHEN E = 1 THEN [VID] END),0)
    ,'E'
        ,2
FROM    @Table1
GROUP BY YEAR([Date])
UNION ALL
SELECT  YEAR([Date])
    ,NULLIF(COUNT(CASE WHEN F = 1 THEN [PID] END),0)
    ,NULLIF(COUNT(CASE WHEN F = 1 THEN [VID] END),0)
    ,'F'
    ,3
FROM    @Table1
GROUP BY YEAR([Date])
UNION ALL
SELECT  YEAR([Date])
    ,NULLIF(COUNT(CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN [PID] END),0)
    ,NULLIF(COUNT(CASE WHEN COALESCE([A], [B], [C], [D], [E], [F]) IS NULL THEN [VID] END),0)
    ,'ALL_NULL'
    ,4
FROM    @Table1
GROUP BY YEAR([Date])
ORDER BY YEAR([Date]),[sl_no]

Output :-

Year    Count_PID   Count_VID   alpha       sl_no
-------------------------------------------------
2013    NULL        NULL        A_to_D      1
2013    NULL        NULL        E           2
2013    NULL        NULL        F           3
2013    2           2           ALL_NULL    4
2014    1           1           A_to_D      1
2014    NULL        NULL        E           2
2014    NULL        NULL        F           3
2014    NULL        NULL        ALL_NULL    4
2015    2           2           A_to_D      1
2015    1           1           E           2
2015    NULL        NULL        F           3
2015    NULL        NULL        ALL_NULL    4
2016    1           1           A_to_D      1
2016    NULL        NULL        E           2
2016    1           1           F           3
2016    1           1           ALL_NULL    4

How to unpivot columns and GROUP BY based on values in SQL Server?

6 Answers6