SQL Server：将行转换为一行

问题描述：

我知道SQL Server Pivot，但是这次我想我需要更复杂的东西，然后是一个简单的pivoted值。SQL Server：将行转换为一行

Here it is an SQL fiddle example.

所以我有一个表，我有最多3行一种startcity - endcity组合。

在查询中，我需要在一行中获得这些组合，而我只需要那些组合，其中有3行。

请指教，

您是否拥有此表的PrimaryKey。你如何找到行之间的差异。 – adopilot 2015-01-20 22:05:56

http://sqlfiddle.com/#!3/07d1d/26 – Mihai 2015-01-20 22:17:11

我对于开始，结束城市和日期有复杂的PK。 – martonx 2015-01-20 22:21:33

答

您可以像这样做一个交叉表。

with NumberedValues as 
(
    SELECT EndCity 
     , StartDate 
     , EndDate 
     , Price 
     , ROW_NUMBER() OVER(PARTITION BY StartCity, EndCIty ORDER BY StartDate) as RowNum 
    FROM [dbo].[BestPrice] 
) 
SELECT EndCity, 
    max(CASE WHEN RowNum = 1 THEN StartDate END) as StartDate1, 
    max(CASE WHEN RowNum = 1 THEN Enddate END) as EndDate1, 
    max(CASE WHEN RowNum = 1 THEN Price END) as Price1, 
    max(CASE WHEN RowNum = 2 THEN StartDate END) as StartDate2, 
    max(CASE WHEN RowNum = 2 THEN Enddate END) as EndDate2, 
    max(CASE WHEN RowNum = 2 THEN Price END) as Price2, 
    max(CASE WHEN RowNum = 3 THEN StartDate END) as StartDate3, 
    max(CASE WHEN RowNum = 3 THEN Enddate END) as EndDate3, 
    max(CASE WHEN RowNum = 3 THEN Price END) as Price3 
FROM NumberedValues 
group by EndCity

SQL Server：将行转换为一行

相关推荐