nuwan.danushka nuwan.danushka - 4 months ago 8
SQL Question

Clarification on SQL table

This is a table that i am going to access.

Account | col1 | col2 | col3
.............................
value 1 x1 x2 x3
value 2 x1 x2 x3


What i want to do is i want convert this table into this format as i mentioned below,

Account | Carrier | |
........................
value 1 col1 x1
value 1 col2 x2
value 1 col3 x3


How do i do this by using select sql. i really appreciate if anyone can help me to solve this problem

Thanks,

Answer

Do a SELECT for each carrier. UNION ALL the results together:

select Account, 'col1' as carrier, col1 from tablename
union all
select Account, 'col2' as carrier, col2 from tablename
union all
select Account, 'col3' as carrier, col3 from tablename
order by Account, carrier