SQL query ; horizontal to vertical

Question

I'm stuck with a SQL query (SQL Server) that involves converting horizontal rows to vertical rows

Below is my data

No      Flag_1    Flag_2    Flag_3
---      ----      -----     -----
A         1         2         3
B         4         1         6

After conversion , the table should be

No     FlagsName    Flag_value
--      ----        ----------
A        Flag_1        1
A        Flag_2        2  
A        Flag_3        3
B        Flag_1        4
B        Flag_2        1
B        Flag_3        6

Any input on this would be helpful?

I'm trying to play around ROW_NUMBER over partition. but it is not working somehow !!!

Thanks !!!

Taryn · Accepted Answer · 2012-09-19T19:55:25.153

16

You can use a UNION ALL:

select No, 'Flag_1' as FlagName, Flag_1 as Flag_Value
from yourtable
union all
select No, 'Flag_2' as FlagName, Flag_2 as Flag_Value
from yourtable
union all
select No, 'Flag_3' as FlagName, Flag_3 as Flag_Value
from yourtable

Or an UNPIVOT:

select no, FlagsName, flag_value
from yourtable
unpivot
(
    flag_value
    for FlagsName in (Flag_1, Flag_2, Flag_3)
) u

See SQL Fiddle With Demo

edited Sep 19 '12 at 19:55

answered Sep 19 '12 at 19:49

Taryn

242,637
56
362
405

1

I like using UNPIVOT as it reads better and also concise. So, how do they compare with each other performance-wise? – Avid Programmer Mar 02 '20 at 12:45

SQL query ; horizontal to vertical

1 Answers1

See SQL Fiddle With Demo

Linked