如何在SQL Server中将列转换为行并根据值进行分组？

Question

如何在SQL Server中将列转换为行并根据值进行分组？

4

我可以帮您翻译成中文。需要计算每年不同 PID 和 VID 的数量。

条件：

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)

在输出中，我希望有一个新的单独列Alpha，其取值如下：

A_to_D, E, F and ALL_Null

表格当前的输出结果：

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

这里：

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)

期望输出结果：

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

- rohit patil

1

为什么“F”和“ALL_NULL”的Count_VID（2016）为空？另外，“A_to_D”的Count_VID / PID（2016）为什么是2？ - Abdul Rasheed

是的，那么关于这个标志有什么问题？它应该表示计算VID的时候，什么情况下不应该计数。你们对预期输出在不同年份之间的一致性并不一致。 - Matt

你是如何让2016年的A_to_D计数为2的？ - Valli

6个回答

0

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

- Bijan Ghasemi

它不会计算PID或VID的不同数量。如果我们插入第一条记录两次，则结果会改变。 - rohit patil

0

我按照以下简单步骤得到了结果：

1）pid_prep - 获取年份、PID和计数 2）vid_prep - 获取年份、VID和计数 3）pid_pivoted - 为PID旋转计数 4）vid_pivoted - 为VID旋转计数 5）主要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 ;

输出如下。如果您需要任何解释，请告诉我。

- Ramesh

0

我正在使用您的示例数据。在某些情况下，您的输出是错误的。请尝试使用各种示例数据运行我的脚本。

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”作为计算列（或者可能是永久列）中的“Year（日期）”，以便在未来利用索引。这只是一个想法。如果您使用“Year（日期）”而不是“Years”，那么输出也将是OK的。

- KumarHarsh

你的#年表是错误的，因为它将2014年至2017年放入其中，而不是2013年至2016年。问题在于添加了ROW_NUMBER()函数，你需要减去1来使其从0开始计数。 - Matt

我很高兴你纠正了它，这是一个简单的错误，我只是想帮助你识别并纠正它。我猜你的意思是“现在”，而不是“没有”，因为你直到我的评论之后才进行了更正，但无论如何，这都改善了你的答案，这也是我的目标，干杯！ - Matt

0

说实话，我对标志有点迷惑，因为在识别 VID 的 DISTINCT 计数时，似乎你每年都没有以相同的方式处理它。所以如果你能详细说明一下，我可以很容易地帮助你确定在哪里使用它作为过滤 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)

查询：

;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

注意：如果一行符合多个条件，则在结果中将计算多次！

链接显示它的工作原理：http://rextester.com/ITVT9711

如果要将标志移动到不同位置以进行过滤或更改cteMeetsCriteria，以便根据alpha的内容以不同方式处理标志非常容易。

- Matt

0

您可以使用简单的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]

输出：

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

- Abdul Rasheed

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Paul Maxwell · Accepted Answer

我选择使用公共表达式（cte）来保存基本计数，这些计数是使用针对8个不同条件（4个PID和4个VID）的case表达式形成的。然后，cte被用作年份维度的来源，该维度是需要与4个字母标签列表进行交叉连接的最终结果所需的。然后再次使用cte（两次）-取消极化-以使计数左连接到所请求的最终行结构中。该结果中的空值是故意的，但如果需要，在最终select子句中可以使用coalesce（）或isnull（）替换为空字符串。请注意，我更喜欢使用cross apply和values“极化”，因为它允许产生的行的布局几乎是所见即所得的，而且它至少与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的详细信息，请参见Brad Schultz的《聚焦UNPIVOT，第1部分》。

最内层查询：

查看初始结果以帮助跟踪后续操作可能会很有用。这是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) |