SQL query ; horizontal to vertical
25,466
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
Author by
user1141584
Updated on July 09, 2022Comments
-
user1141584 almost 2 years
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 !!!