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?
mariecAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:

Have you considered UNPIVOT?
Check this link for reference:
 
http://msdn.microsoft.com/en-us/library/ms177410.aspx

SELECT User, oldcolumn, Value 
FROM 
   (SELECT User, Value1, Value2, Value3
   FROM yourtable) p
UNPIVOT
   (Value FOR oldcolumn IN 
      (Value1, Value2, Value3)
)AS unpvt;

Open in new window

0
 
RiteshShahCommented:

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
0
 
mariecAuthor Commented:
This is great! I even did some basic performance tests and UNPIVOT is fastest way to get that data. Perfect! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.