我看了几十个类似的换位问题的一个解决方案，我将提议，但奇怪的是没有恰好反映了我的问题。 我只是试图翻转我行列一个简单的仪表盘类型的数据集。
从各种交易表牵拉时的数据是这样的：
DatePeriod PeriodNumberOverall Transactions Customers Visits
'Jan 2012' 1 100 50 150
'Feb 2012' 2 200 100 300
'Mar 2012' 3 300 200 600
我希望能够产生如下：
Jan 2012 Feb 2012 Mar 2012
Transactions 100 200 300
Customers 50 100 200
Visits 150 300 600
该指标将是静态的（交易，客户和访问），但日期时间将是动态的（IE - 更添加个月去了）。
再次，我已经准备好很多例子撬动支点，UNPIVOT，存储过程，UNION承滴盘等，但没有在那里我没有做任何的聚集，只是从字面上调换整个输出。 我还发现一个简单的方法在Visual Studio 2005中做到这一点使用带有嵌入式列表中的矩阵，但我不能导出最终输出到Excel这是一个必要条件。 任何帮助将不胜感激。
Answer 1:
为了得到你想要的结果，你需要首先UNPIVOT数据，然后PIVOT the DatePeriod`值。
该UNPIVOT将变换的多列Transactions ， Customers和Visits为多行。 其他答案是使用UNION ALL以UNPIVOT但SQL Server 2005是第一年的UNPIVOT功能得到了支持。
到逆转置数据的查询是：
select dateperiod,
col, value
from transactions
unpivot
(
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
) d
pivot
(
sum(value)
for dateperiod in ([Jan 2012], [Feb 2012], [Mar 2012])
) piv
order by SortOrder;
请参阅SQL拨弄演示 。
如果你有数目不详的日期期间的，那么你将使用动态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拨弄演示 。 双方将给出结果：
| COL | JAN 2012 | FEB 2012 | MAR 2012 |
-------------------------------------------------
| Transactions | 100 | 200 | 300 |
| Customers | 50 | 100 | 200 |
| Visits | 150 | 300 | 600 |
Answer 2:
您需要动态地创建一个SQL语句PIVOT和APPLY的飞行运营商，然后运行该命令。 如果您的静态指标（交易，客户和访问），因此我们可以使用CROSS APPLY运算符值作为一个表源。
对于SQL Server2008的+
DECLARE @cols nvarchar( max),
@query nvarchar(max)
SELECT @cols =
STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName
FROM dbo.test62 t
FOR XML PATH(''), TYPE).value ('.', 'nvarchar(max)'), 1, 1, '')
SET @query =
'SELECT *
FROM (
SELECT t.DatePeriod, COALESCE(o.Transactions, o.Customers, o.Visits) AS PvtVals, o.PvtColumns, o.OrderColumns
FROM dbo.test62 t CROSS APPLY (
VALUES(t.Transactions, NULL, NULL, ''Transaction'', 1),
(NULL, t.Customers, NULL, ''Customers'', 2),
(NULL, NULL, t.Visits, ''Visits'', 3)
) o (Transactions, Customers, Visits, PvtColumns, OrderColumns)
) p
PIVOT
(
MAX(PvtVals) FOR DatePeriod IN (' + @cols + ')
) AS pvt
ORDER BY pvt.OrderColumns '
EXEC(@query)
结果：
PvtColumns Jan 2012 Fed 2012 Mar 2012
Transaction 100 200 300
Customers 50 100 200
Visits 150 300 600
演示上SQLFiddle
对于SQL Server 2005
DECLARE @cols nvarchar( max),
@query nvarchar(max)
SELECT @cols =
STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName
FROM dbo.test62 t
FOR XML PATH(''), TYPE).value ('.', 'nvarchar(max)'), 1, 1, '')
SET @query =
'SELECT *
FROM (
SELECT t.DatePeriod, COALESCE(o.Transactions, o.Customers, o.Visits) AS PvtVals, o.PvtColumns, o.OrderColumns
FROM dbo.test62 t CROSS APPLY (
SELECT t.Transactions, NULL, NULL, ''Transaction'', 1
UNION ALL
SELECT NULL, t.Customers, NULL, ''Customers'', 2
UNION ALL
SELECT NULL, NULL, t.Visits, ''Visits'', 3
) o (Transactions, Customers, Visits, PvtColumns, OrderColumns)
) p
PIVOT
(
MAX(PvtVals) FOR DatePeriod IN (' + @cols + ')
) AS pvt
ORDER BY pvt.OrderColumns'
EXEC(@query)
Answer 3:
如果你能提前知道有多少不同的日期期间，那么你可以使用像下面的固定查询：
;with CTE_UNIONTable
as
(
select [DatePeriod],[PeriodNumberOverall],[Transactions] as [value], 'Transactions' as subType from table1
UNION ALL
select [DatePeriod],[PeriodNumberOverall],[Customers] as [value], 'Customers' as subType from table1
UNION ALL
select [DatePeriod],[PeriodNumberOverall],[Visits] as [value], 'Visits' as subType from table1
), CTE_MiddleResult
as
(
select * from CTE_UNIONTable
pivot
(
max(value)
for DatePeriod in ([Jan 2012],[Feb 2012],[Mar 2012])
) as P
)
select SubType, max([Jan 2012]) as [Jan 2012] ,max([Feb 2012]) as [Feb 2012], max([Mar 2012]) as [Feb 2012]
from CTE_MiddleResult
group by SubType
SQL FIDDLE DEMO
如果多少时间周期是不可预测的，那么@Alexander已经给解决，下面的代码是只是一种意见，而是采用适用 ，使用UNION ALL
DECLARE @cols nvarchar( max),
@query nvarchar (max),
@selective nvarchar(max)
SELECT @cols =
STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName
FROM table1 t
FOR XML PATH( ''), TYPE).value ('.', 'nvarchar(max)'),1,1,'')
SELECT @selective =
STUFF((SELECT ',MAX(' + QUOTENAME(t.DatePeriod) +') as ' + QUOTENAME(t.DatePeriod) AS ColName
FROM table1 t
FOR XML PATH( ''), TYPE).value ('.', 'nvarchar(max)'),1,1,'')
set @query = '
;with CTE_UNIONTable
as
(
select [DatePeriod],[PeriodNumberOverall],[Transactions] as [value], ''Transactions'' as subType from table1
UNION ALL
select [DatePeriod],[PeriodNumberOverall],[Customers] as [value], ''Customers'' as subType from table1
UNION ALL
select [DatePeriod],[PeriodNumberOverall],[Visits] as [value], ''Visits'' as subType from table1
), CTE_MiddleResult
as
(
select * from CTE_UNIONTable
pivot
(
max(value)
for DatePeriod in ('+@cols+')
) as P
)
select SubType,' + @selective + '
from CTE_MiddleResult
group by SubType'
exec(@query)
SQL FIDDLE DEMO
文章来源: Displaying Columns as Rows in SQL Server 2005
