帮助使用 PIVOT 查询

Question

我有一张表，结构如下：

CREATE TABLE [dbo].[AUDIT_SCHEMA_VERSION](
    [SCHEMA_VER_MAJOR] [int] NOT NULL,
    [SCHEMA_VER_MINOR] [int] NOT NULL,
    [SCHEMA_VER_SUB] [int] NOT NULL,
    [SCHEMA_VER_DATE] [datetime] NOT NULL,
    [SCHEMA_VER_REMARK] [varchar](250) NULL
);

一些示例数据（似乎与SQLFiddle有问题..因此提供一些示例数据）：

INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,7,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20140417 18:10:44.100' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,5,0,CAST('20140417 18:14:14.157' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,6,0,CAST('20140417 18:14:23.327' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,7,0,CAST('20140417 18:14:32.270' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,8,0,CAST('20141209 09:38:40.700' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,9,0,CAST('20141209 09:43:04.237' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,10,0,CAST('20141209 09:45:19.893' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,13,0,CAST('20150323 14:54:30.847' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,14,CAST('20140417 18:11:07.977' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,15,CAST('20140417 18:11:13.130' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,2,0,CAST('20140417 18:12:11.200' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,3,0,CAST('20140417 18:12:33.330' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,4,0,CAST('20140417 18:12:48.803' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,13,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,11,0,CAST('20141209 09:45:58.993' as DATETIME),'Stored procedure build')
INSERT INTO [AUDIT_SCHEMA_VERSION]([SCHEMA_VER_MAJOR],[SCHEMA_VER_MINOR],[SCHEMA_VER_SUB],[SCHEMA_VER_DATE],[SCHEMA_VER_REMARK])
VALUES(2,12,0,CAST('20141209 09:46:50.070' as DATETIME),'Stored procedure build');

这里是带有一些示例数据的 SQLFiddle。 有没有熟悉 T-sql 的人可以指导我如何实现最终结果？我知道使用 PIVOT（带有动态列）是正确的方法，但是我无法弄清楚。 预期结果：

到目前为止，我有以下内容：

select row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_DATE 
        ) as rownum
    ,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
    ,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build'
order by UPGRADE_DATE 

- Kin Shah

Answer 1

这个最终结果有点混乱，因为每个日期都有多个SCHEMA_VER。在我展示如何使用动态SQL来完成之前，我先演示一下如何使用静态代码来正确实现逻辑。为了得到最终结果，你可以同时使用pivot和unpivot。 但首先，我会修改你的原始查询，使用以下内容：

select 
    row_number() over (
    partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
    ) as minrownum
, row_number() over (
    partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
    ) as maxrownum
,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build';

查看SQL Fiddle with Demo。我使用row_number()来获取每个日期的第一个和最后一个SCHEMA_VER。这样做是为了只将这些值连接在一起作为注释。 然后，我会使用临时表来存储具有minrownum和maxrownum为1的行。临时表将包含upg_date和comment。该注释列包含每个日期的SCHEMA_VER对的连接字符串。

create table #srcData
(
    upg_date varchar(10),
    comment varchar(500)
);

填充临时表的代码将如下所示：

;with cte as
(
  select 
        row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR, SCHEMA_VER_MINOR, SCHEMA_VER_SUB
        ) as minrownum
    , row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_MAJOR desc, SCHEMA_VER_MINOR desc, SCHEMA_VER_SUB desc
        ) as maxrownum
    ,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
    ,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
  from audit_schema_version
  where SCHEMA_VER_REMARK like 'Stored procedure build'
)
insert into #srcData
select distinct
    c1.UPG_DATE,
    comment 
        = STUFF((
                  SELECT ' - ' + c2.SCHEMA_VER 
                  FROM cte c2
                  WHERE (c2.minrownum = 1 or c2.maxrownum = 1)
                    and c1.upg_date = c2.upg_date
                  order by c2.minrownum
                  FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') 
from cte c1
where c1.minrownum = 1 or c1.maxrownum = 1;

这次对数据的首次处理将为您带来以下结果：

|   upg_date |           comment |
|------------|-------------------|
| 03-23-2015 |            2.13.0 |
| 04-05-2013 |  1.6.13 - 1.16.13 |
| 04-17-2014 |   1.16.13 - 2.7.0 |
| 12-09-2014 |    2.8.0 - 2.12.0 |

现在你还需要得到每年的每个日期和完整连接的评论的计数。这就是使用unpivot的地方。你可以使用以下代码来为每年创建完整的评论并获得计数。

select distinct 
    Yr =  right(s1.upg_date, 4),
    cnt = count(*) over(partition by right(s1.upg_date, 4)),
    fullcomment 
            = STUFF((
                      SELECT '; ' + s2.comment 
                      FROM #srcData s2
                      WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
                      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') 
from #srcData s1;

请参见带演示的SQL Fiddle。数据现在如下所示：

|   Yr | cnt |                       fullcomment |
|------|-----|-----------------------------------|
| 2013 |   1 |                  1.6.13 - 1.16.13 |
| 2014 |   2 |  1.16.13 - 2.7.0;  2.8.0 - 2.12.0 |
| 2015 |   1 |                            2.13.0 |

如您所见，您有多个需要进行透视的列，因此您可以将fullcomment和cnt列都解开成多行。这可以使用UNPIVOT函数或CROSS APPLY来完成。我更倾向于在这里使用CROSS APPLY，因为您希望将值连接在一起以创建新的列名：

;with cte as
(
    select distinct 
        Yr =  right(s1.upg_date, 4),
        cnt = count(*) over(partition by right(s1.upg_date, 4)),
        fullcomment 
                = STUFF((
                          SELECT '; ' + s2.comment 
                          FROM #srcData s2
                          WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
                          FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '') 
    from #srcData s1
) 
select [2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment]
from
(
    select c.col, val
    from cte d
    cross apply
    (
        values 
            (Yr, cast(cnt as nvarchar(50))),
            (Yr+'_comment', fullcomment)
    ) c (col, val)  
) d
pivot
(
    max(val)
    for col in ([2015], [2015_comment], [2014], [2014_comment], [2013], [2013_comment])
) piv;

看一下SQL Fiddle with Demo。 一旦你掌握了逻辑，你可以很容易地将其转换为动态SQL。

-- get list of the columns
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(col) 
                    from #srcData
                    cross apply
                    (
                        select right(upg_date, 4), right(upg_date, 4), 2 union all
                        select right(upg_date, 4), right(upg_date, 4)+'_comment', 1
                    ) c (yr, col, so)
                    group by yr, col, so
                    order by yr desc, so desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
    = 'SELECT ' + @cols + ' 
        from 
        (
            select c.col, val
            from
            (
                select distinct 
                    Yr =  right(s1.upg_date, 4),
                    cnt = count(*) over(partition by right(s1.upg_date, 4)),
                    fullcomment 
                            = STUFF((
                                      SELECT ''; '' + s2.comment 
                                      FROM #srcData s2
                                      WHERE right(s1.upg_date, 4) = right(s2.upg_date, 4)
                                      FOR XML PATH(''''), TYPE).value(''.[1]'', ''nvarchar(max)''), 1, 2, '''') 
                from #srcData s1
            ) d
            cross apply
            (
                values 
                    (Yr, cast(cnt as nvarchar(50))),
                    (Yr+''_comment'', fullcomment)
            ) c (col, val)  
        ) x
        pivot 
        (
           max(val)
           for col in (' + @cols + ')
        ) p '

exec sp_executesql @query;

查看带演示的SQL Fiddle。两个版本都会给你结果：

| 2015 | 2015_comment | 2014 |                      2014_comment | 2013 |      2013_comment |
|------|--------------|------|-----------------------------------|------|-------------------|
|    1 |       2.13.0 |    2 |  1.16.13 - 2.7.0;  2.8.0 - 2.12.0 |    1 |  1.6.13 - 1.16.13 |

- Taryn

Answer 2

添加解释和一个示例：http://sqlfiddle.com/#!6/c92b2/5。 下面的查询： 1. 使用子查询按日期选择最小和最大版本（最小和最大应用于整数，以确保例如 6 < 16） 2. 然后��择年份（以便后续分组），日期（以便排序）和最小 - 最大版本。

SELECT LEFT(UPG_DATE, 4) AS Year
    , UPG_DATE
    , CONVERT(varchar(1), MIN_VER/1000000) + '.' + CONVERT(varchar(2), (MIN_VER/1000 - (MIN_VER/1000000)*1000)) + '.' + CONVERT(varchar(2), MIN_VER%1000)
        + ' - ' + CONVERT(varchar(1), MAX_VER/1000000) + '.' + CONVERT(varchar(2), (MAX_VER/1000 - (MAX_VER/1000000)*1000)) + '.' + CONVERT(varchar(2), MAX_VER%1000) AS Versions
INTO #Versions
FROM (
    SELECT CONVERT(varchar(10), SCHEMA_VER_DATE, 112) as UPG_DATE
        , MIN(SCHEMA_VER_MAJOR*1000000 + SCHEMA_VER_MINOR*1000 + SCHEMA_VER_SUB) AS MIN_VER
        , MAX(SCHEMA_VER_MAJOR*1000000 + SCHEMA_VER_MINOR*1000 + SCHEMA_VER_SUB) AS MAX_VER
    FROM audit_schema_version
    WHERE SCHEMA_VER_REMARK like 'Stored procedure build'
    GROUP BY CONVERT(varchar(10), SCHEMA_VER_DATE, 112)
) Versions;

接下来，由于每一列都会重复（年份和年份_注释），所以选择两列来识别数据。通过计算日期的数量来了解升级的次数，并按年份对版本进行分组，这样就可以将所有内容放在一行中。这样我们就得到了最终的表格，用于进行数据透视。

SELECT Year, Year + '_COMMENT' as Year_COMMENT
    , COUNT(Year) AS Upgrades
    , STUFF((SELECT ' ; ' + SUB.Versions
                FROM #Versions SUB
                WHERE SUB.Year = V.Year
                ORDER BY UPG_DATE ASC
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(2000)')
            ,1,3,'') Versions
INTO #GroupedResults
FROM #Versions V
GROUP BY Year

SELECT * FROM #GroupedResults

这里是结果：

| Year | Year_COMMENT | Upgrades | Versions                         |
|------|--------------|----------|----------------------------------|
| 2013 | 2013_COMMENT | 1        | 1.6.13 - 1.16.13                 |
| 2014 | 2014_COMMENT | 2        | 1.16.13 - 2.7.0 ; 2.8.0 - 2.12.0 |
| 2015 | 2015_COMMENT | 1        | 2.13.0 - 2.13.0                  |

接下来，一个变量被填充了列，按照我们想要显示的顺序排列：

DECLARE @cols VARCHAR(1000),
    @finalQuery VARCHAR(2000)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(YEAR) + ',' + QUOTENAME(YEAR + '_COMMENT')
                    FROM #GroupedResults
                    GROUP BY YEAR
                    ORDER BY YEAR DESC
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(2000)')
    ,1,1,'')

最后，下面的查询使用了cross apply，所以我们得到了：
1. col列填充了Year和Year_COMMENT的值
2. value列填充了升级次数，在对应年份的行中，以及版本值，在对应Year_COMMENT的行中
我们使用了一个透视表对这两个结果列进行了处理，从而得到了在col列上的值（升级次数与版本交替出现，年份与Year_COMMENT交替出现）

set @finalQuery = N'SELECT ' + @cols + N' from 
             (
                select col, value
                from #GroupedResults
                cross apply
                (
                    SELECT CAST(Upgrades AS VARCHAR(200)), Year
                    UNION ALL
                    SELECT CAST(Versions AS VARCHAR(200)), Year_COMMENT
                ) c (value, col)
            ) x
            pivot 
            (
                Min(value)
                for col in (' + @cols + N')
            ) p1
            ; '

EXEC (@finalQuery);

DROP TABLE #Versions;
DROP TABLE #GroupedResults;

这将返回以下结果：

| 2015 | 2015_COMMENT    | 2014 | 2014_COMMENT                     | 2013 | 2013_COMMENT     |
|------|-----------------|------|----------------------------------|------|------------------|
| 1    | 2.13.0 - 2.13.0 | 2    | 1.16.13 - 2.7.0 ; 2.8.0 - 2.12.0 | 1    | 1.6.13 - 1.16.13 |

- JoseTeixeira