We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

MS SQL 2008 convert columns to rows with a query

Medium Priority
618 Views
Last Modified: 2012-05-06
I have a table with a fixed number of columns as below

ID      DATE      DATA1 DATA2
------------------------------------
1  01/01/2009    100       101
2  01/02/2009    210       221

I need to convert this table to rows as below

ID     DATE      DATA
-------------------------
1  01/01/2009   100
1  01/01/2009   101
2  01/02/2009   210
2  01/02/2009   221

Is it possible to do this with a query?
Comment
Watch Question

Commented:
Hi,

How about:

SELECT ID, DATE, DATA1 AS DATA FROM table1
UNION ALL
SELECT ID, DATE, DATA2 FROM table1

/peter

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Try this code
SELECT ID, DATE, DATA
FROM 
   (SELECT ID, DATA
   FROM urtable) p
UNPIVOT
   (DATA FOR DATE IN 
      ([01/01/2009], [01/02/2009])
)AS unpvt;

Open in new window

Author

Commented:
Thanks pivar that's a nice solution, I've never used UNION before!

/peter
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
how about this:

select id, [date], datacol, data from 
	(select id, [date], data1, data2 from ##test) as p
unpivot
	(data for datacol in (data1, data2)
	) as unpvt

Open in new window

ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
forgot to replace my temp table name with a placeholder, so replace ##test with the name of your table...
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
ouch, an answer was even already accepted, sorry about that (that happens when people get interrupted while working on a solution :-)
Well, I guess there's now alternative solution that shows how to use the unpivot keyword without hardcoded data.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.