Solved

Split row columns into multiple rows of one column

Posted on 2009-05-06
3
1,019 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

636 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