Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
memorykills
Starting Member
18 Posts |
 Posted - 2011-09-12 : 11:14:29
|
| Hi friends, I need help with a query:I currently have a table in database which is designed as:Create Table Experiment_History( PROD_ID char(5), -- this is the primary key Date_1 datetime, Value_1 int, Date_2 datetime, Value_2 int, Date_3 datetime, Value_3 int, .... Date_20 datetime, Value_20 int)So, this table will store 20 times experiments' value and date.I need to create a table with the data from aforementioned table and the new table should look like this:Prod_ID Date Value------------------00001 1/1/2011 20000001 1/12/2011 12500001 3/1/2011 212 ..00002 1/2/2011 34...I am currently using this query to convert the table:SELECT PROD_ID, Date_1 AS Date, Value_1 AS ValueUNION ALLSElECT PROD_ID, Date_2, Value_2UNION ALLSELECT PROD_ID, Date_3, Value_3...(totally, 19 UNION ALL statements..)But it looks really stupid and lengthy. Is there a better way to do this table conversion job?Thank you very much. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
memorykills
Starting Member
18 Posts |
Posted - 2011-09-12 : 11:41:50
|
| thanks tkizer.So my query with 19 UNION ALL is the only solution? :-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
memorykills
Starting Member
18 Posts |
Posted - 2011-09-12 : 11:52:53
|
| thanks tkizer. I have no control over the design of the original table, which is used in production database and the admin doesn't want to make any change with it.I am working on a report database copying production database tables to the report database. :-) |
|
|
|
|
|
|
|
|
help needed for a query