?
Solved

MS SQL 2008 convert columns to rows with a query

Posted on 2009-02-13
6
Medium Priority
?
600 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?
0
Comment
Question by:petlar930
6 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 23632938
Hi,

How about:

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

/peter

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23632947
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

0
 

Author Closing Comment

by:petlar930
ID: 31546581
Thanks pivar that's a nice solution, I've never used UNION before!

/peter
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 23633235
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

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 23633242
forgot to replace my temp table name with a placeholder, so replace ##test with the name of your table...
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 23633270
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 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