SQL Server中的转置行 [英] Transpose Rows in SQL Server

问题描述

我有一个表Table1，如下所示

I have a table Table1 as follows

ID	Action 	Value
1	BEGIN	1
1	END	5
2	BEGIN	20
2	END	25
1	BEGIN	10
1	END	15

请帮助我编写一个创建结果集的TSQL查询，如下所示.

Please help me to write a TSQL query which create a result set as follows.

ID	BEGIN	END
1	1	5
2	20	25
1	10	15

推荐答案

使用PIVOT-UNPIVOT

使用PIVOT和UNPIVOT [在SQL Server中透视数据 [

Use PIVOT - UNPIVOT

Using PIVOT and UNPIVOT[^]

Pivoting data in SQL Server[^](Alternate ways)

解决方案:1

Solution: 1

select * from (
select ID,[Action],value from @TableData) s_tab pivot (
max(value)  for [Action] in ([BEGIN],[END])) as pvt

union

select * from (
select ID,[Action],value from @TableData) s_tab pivot (
min(value)for [Action] in ([BEGIN], [END])) as pvt

解决方案2

Solution 2

--Select Query
SELECT Beginning.ID AS [ID]
         ,Beginning.Value AS [Begin]
         ,Ending.Value AS [End]
FROM @Table1 AS Beginning
      OUTER APPLY (
                        SELECT TOP 1 Value
                        FROM @Table1 AS ext
                        WHERE ext.[Action] = 'END'
                                AND ext.ID = Beginning.ID
                                AND ext.Value > Beginning.Value
                        ORDER BY ext.Value ASC
                   ) AS Ending
WHERE [Action] = 'BEGIN'

应该使用PIVOT完成

阅读此内容

http://msdn.microsoft.com/en-us/library/ms177410.aspx [ ^ ]

This should be accomplish using PIVOT

read this

http://msdn.microsoft.com/en-us/library/ms177410.aspx[^]

这篇关于SQL Server中的转置行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！