Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Split row columns into multiple rows of one column

Posted on 2009-05-06
3
Medium Priority
?
1,025 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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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 …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

719 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