本文介绍了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中的转置行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 22:15