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-19 19:55:25Z

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

I like using UNPIVOT as it reads better and also concise. So, how do they compare with each other performance-wise?

Collectives™ on Stack Overflow

SQL query ; horizontal to vertical

1 Answer 1

See SQL Fiddle With Demo

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

See SQL Fiddle With Demo

1 Comment

Linked

Related