Sql-Server

幫助 PIVOT 查詢

  • September 29, 2016

我有一個具有以下結構的表：

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**](http://sqlfiddle.com/#!6/9f84d)一些範例數據。

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

在此處輸入圖像描述

獲得最終結果有點混亂，因為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每個日期的第一個和最後一個。這是必需的，因此您可以僅將這些值連接在一起以進行評論。

然後我會使用一個臨時表來儲存 aminrownum和maxrownum1 的行。臨時表將包含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 with Demo。數據現在看起來像：

|   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 來完成。我更喜歡在這裡交叉應用，因為您需要將值連接在一起以創建新的列名：

;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 with Demo。兩個版本都會給你結果：

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

引用自：https://dba.stackexchange.com/questions/100899

相關問答

Sql-Server

如何在 SQL Server 中將一列中的值分成兩列

  • November 20, 2018
檢視問答
Sql-Server

使用 SQL Server 進行 PIVOT

  • July 2, 2018
檢視問答
Sql-Server

創建基於樞軸的變數

  • July 24, 2017
檢視問答
Sql-Server

在 SQL Server 2008 中生成數據透視表

  • June 14, 2017
檢視問答
Sql-Server

交叉表樞軸與否？

  • December 2, 2015
檢視問答
Sql-Server

如何旋轉這個 unpivot？

  • November 13, 2015
檢視問答