SQL Server中的转置行

本文介绍了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

推荐答案

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'

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