Solved

Split row columns into multiple rows of one column

Posted on 2009-05-06
3
985 Views
Last Modified: 2013-11-05
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?
0
Comment
Question by:mariec
3 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24317275

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
 
LVL 41

Accepted Solution

by:
ralmada earned 125 total points
ID: 24317436

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
 

Author Closing Comment

by:mariec
ID: 31578575
This is great! I even did some basic performance tests and UNPIVOT is fastest way to get that data. Perfect! Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now