All geek questions in one place

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

I want to calculate different PID and VID values ​​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 Alpha column with the following values:

A_to_D, E, F and ALL_Null

Current table output:

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 Result:

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
+4
source share
6 answers

common table expression (cte) , case 8 (4 PID 4 VID). cte , , 4 -. cte () - - . , , , coalesce() isnull() select. . "" cross apply values, WYSIWYG , , , , unpivot ().

: 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)
;

:

/* 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
;

:

| 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 |

, CROSS APPLY VALUES UNPIVOT, . UNPIVOT, 1 Brad Schultz

:

, . cte :

/* 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]
;

:

| 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) |
+3
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
0

,

1) pid_prep - , PID 2) vid_prep - , VID 3) pid_pivoted - PID 4) vid_pivoted - VID 5) main SELECT - pid_pivoted vid_pivoted, .

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 ;

. , , .

0

. . script .

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

: Years as year (date) ( ), . .

Year (Dates) Years, OK

0

, , , , DISTINCT VID. , , , VID . . , - , , .

:

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

, 1 , !

, , : http://rextester.com/ITVT9711

If it’s very easy to move the flag to different locations to filter or change cteMeetsCriteria, to be able to handle the flag in different ways depending on what alpha is.

0
source

You can reach this conclusion using a simple query UNION,

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
0
source

Source: https://habr.com/ru/post/1688995/

More articles:


All Articles