为了获得所需的结果，您需要首先提供
UNPIVOT数据，然后是
PIVOT theDatePeriod`值。
该UNPIVOT将变换的多列
Transactions，
Customers并
Visits为多行。其他答案使用a
UNIOnALL取消，但是SQL Server 2005
UNPIVOT是支持该功能的第一年。
要取消数据透视表的查询是：
select dateperiod, col, valuefrom transactionsunpivot( value for col in (Transactions, Customers, Visits)) u
参见演示。这会将您当前的列转换为多行，因此数据如下所示：
| DATEPERIOD | COL | VALUE |-------------------------------------| Jan 2012 | Transactions | 100 || Jan 2012 | Customers | 50 || Jan 2012 | Visits | 150 || Feb 2012 | Transactions | 200 |
现在，由于数据在行中，因此您可以将
PIVOT函数应用于
DatePeriod列：
select col, [Jan 2012], [Feb 2012], [Mar 2012]from( select dateperiod, t.col, value, c.SortOrder from ( select dateperiod, col, value from transactions unpivot ( value for col in (Transactions, Customers, Visits) ) u ) t inner join ( select 'Transactions' col, 1 SortOrder union all select 'Customers' col, 2 SortOrder union all select 'Visits' col, 3 SortOrder ) c on t.col = c.col) dpivot( sum(value) for dateperiod in ([Jan 2012], [Feb 2012], [Mar 2012])) pivorder by SortOrder;
请参阅带有演示的SQL Fiddle。
如果您有未知的日期期限，那么您将使用动态SQL：
DECLARE @cols AS NVARCHAr(MAX), @query AS NVARCHAr(MAX)select @cols = STUFF((SELECT ',' + QUOTENAME(dateperiod) from transactions group by dateperiod, PeriodNumberOverall order by PeriodNumberOverall FOR XML PATH(''), TYPE ).value('.', 'NVARCHAr(MAX)') ,1,1,'')set @query = 'SELECt col, ' + @cols + ' from ( select dateperiod, t.col, value, c.SortOrder from ( select dateperiod, col, value from transactions unpivot ( value for col in (Transactions, Customers, Visits) ) u ) t inner join ( select ''Transactions'' col, 1 SortOrder union all select ''Customers'' col, 2 SortOrder union all select ''Visits'' col, 3 SortOrder ) c on t.col = c.col ) x pivot ( sum(value) for dateperiod in (' + @cols + ') ) p order by SortOrder'execute(@query)请参阅带有演示的SQL Fiddle。两者都会给出结果：
| COL | JAN 2012 | FEB 2012 | MAR 2012 |-------------------------------------------------| Transactions | 100 | 200 | 300 || Customers | 50 | 100 | 200 || Visits | 150 | 300 | 600 |
