Split row columns into multiple rows of one column
I have a table which looks something like this:
User VALUE1 VALUE2 VALUE3
------ ---------- ----------- -----------
Tom 4 5 5
Jane 3 3 3
Kate 5 3 4
It's bad design, I know... but was this way before I got here and now I'd need to have a query return something like this:
User VALUE
------ ---------
Tom 4
Tom 5
Tom 5
Jane 3
Jane 3
Jane 3
Kate 5
Kate 3
Kate 4
Is this possible in SQL Server through a SELECT statement?
User VALUE1 VALUE2 VALUE3
------ ---------- ----------- -----------
Tom 4 5 5
Jane 3 3 3
Kate 5 3 4
It's bad design, I know... but was this way before I got here and now I'd need to have a query return something like this:
User VALUE
------ ---------
Tom 4
Tom 5
Tom 5
Jane 3
Jane 3
Jane 3
Kate 5
Kate 3
Kate 4
Is this possible in SQL Server through a SELECT statement?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is great! I even did some basic performance tests and UNPIVOT is fastest way to get that data. Perfect! Thanks
select name,val1 as val from tablename
union
select name,val2 as val from tablename
union
select name,val3 as val from tablename
order by name