CODEWITHSUNDEEP
sql-serverpivot
martonx
martonx

Reputation: 2011

SQL Server : convert rows to one row

I know SQL Server Pivot, but this time I think I will need something more complex then a simple pivoted value.

Here it is an SQL fiddle example.

So I have a table where I have maximum 3 rows for one startcity - endcity combination.

In a query I need to get these combinations in just one row, and I need just those combinations, where there are 3 rows.

Please advice,

Upvotes: 0

Views: 56

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

You can do this as a crosstab like this.

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

Upvotes: 1

Related Questions