Convert vertical table to horizontal in SQL Server

Question

I have a large table that has 8 row numbers with an associated loss and carrier. I am trying to convert into a horizontal structure.

CREATE TABLE #mytable ( [ID] int NULL, [RowNum] bigint NULL, [Loss] float NULL, [Carrier] nvarchar(255) NULL ) INSERT INTO #mytable ([ID], [RowNum], [Loss], [Carrier]) VALUES (1,1, 0, 'test1'), (1,2, NULL, 'test2'), (1,3, 1.95, 'test3'), (1,4, 51, 'test4'), (1,5, 105.75, 'test5'), (1,6, 0, 'test6'), (1,7, 173, 'test7'), (1,8, 256.35, 'test8'), (2,1, 33158.3, 'test1'), (2,2, 7925396, 'test2'), (2,3, 0, 'test3'), (2,4, NULL, 'test4'), (2,5, 2461684, 'test5'), (2,6, 159392, 'test6'), (2,7, 14791, 'test7'), (2,8, 14555, 'test8');

I am trying to get a horizontal table like the following (per id and a horizontal structure for Loss and Carrier):

horizontalstructure

I was trying case when end statements, but I wasn't achieving the desired results.

Can someone please help? I appreciate it.

Sean Lange · Accepted Answer · 2018-10-18 13:48:16Z

This has been asked and answered hundreds of times. But it was easier to code a solution than point you to a duplicate. Excellent job posting table structure, sample data and desired output!!!

The easiest solution is using conditional aggregation like this.

select ID , Loss1 = max(case when RowNum = 1 then Loss end) , Loss2 = max(case when RowNum = 2 then Loss end) , Loss3 = max(case when RowNum = 3 then Loss end) , Loss4 = max(case when RowNum = 4 then Loss end) , Loss5 = max(case when RowNum = 5 then Loss end) , Loss6 = max(case when RowNum = 6 then Loss end) , Loss7 = max(case when RowNum = 7 then Loss end) , Loss8 = max(case when RowNum = 8 then Loss end) , Carrier1 = max(case when RowNum = 1 then Carrier end) , Carrier2 = max(case when RowNum = 2 then Carrier end) , Carrier3 = max(case when RowNum = 3 then Carrier end) , Carrier4 = max(case when RowNum = 4 then Carrier end) , Carrier5 = max(case when RowNum = 5 then Carrier end) , Carrier6 = max(case when RowNum = 6 then Carrier end) , Carrier7 = max(case when RowNum = 7 then Carrier end) , Carrier8 = max(case when RowNum = 8 then Carrier end) from #mytable group by ID

But it was easier to code a solution than point you to a duplicate ... sometimes true, probably not in this case. You could have spiced things up by using PIVOT though.
@TimBiegeleisen but why use a pivot? It isn't needed, the syntax is obtuse and conditional aggregation usually performs slightly better.
Didn't know about PIVOT's performance issues +1. Agree about its obtuse syntax.
@TimBiegeleisen here is a great article on the topic by Jeff Moden. sqlservercentral.com/articles/T-SQL/63681 The performance differences are pretty minimal but with pre-aggregation it can jump significantly.

Collectives™ on Stack Overflow

Convert vertical table to horizontal in SQL Server

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related