Split two columns in sql into multiple columns

Question

I have data in following format

 ID      Loss        Sum
--------------------------
 1     146276293.1    1
 1     175538865.5    2
 1     146276293.1    3

I want SQL script to return me

   ID      Sum1         Sum2         Sum3
   --------------------------------------------------- 
   1     146276293.1  175538865.5   146276293.1

Case statements or pivot would work if sum is limited to 3 groups if variable in number then dynamic pivot. — xQbert
– xQbert, Commented Sep 10, 2015 at 17:50
Instead of dynamic pivots I prefer to use a dynamic cross tab. The syntax is less obtuse to me. sqlservercentral.com/articles/Crosstab/65048 — Sean Lange
– Sean Lange, Commented Sep 10, 2015 at 18:06

rwking · Accepted Answer · 2015-09-10 17:41:58Z

1

This simple example does what you're trying to do. PIVOT is a great tool. Also research UNPIVOT when attempting the opposite.

select *
from (  
        select 1 id, 100 num, 'Sum1' col
        union select 1, 200, 'Sum2'
        union select 1, 300, 'Sum3' ) x
pivot 
(sum(num) for col in (Sum1, Sum2, Sum3)) p

answered Sep 10, 2015 at 17:41

rwking

1,0326 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Split two columns in sql into multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related