|
|
Posted by Tom Moreau on 03/29/06 20:16
If you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:
select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@gmail.com> wrote in message
news:1143651738.146958.160520@t31g2000cwb.googlegroups.com...
Hello all,
I have a table with thousands of rows and is in this format:
id col1 col2 col3 col4
--- ------ ----- ------ ------
1 nm 78 xyz pir
2 bn 45 abc dir
I now want to get the data from this table in this format:
field val
---------------------------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir
In order to do this I am doing a union:
select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)
the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?
The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.
my main concern is performance. any ideas please?
thanks
Navigation:
[Reply to this message]
|