Optimizing SQL - Union

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

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

Ok, I will try this. I am a liitle bit confused about the cross join..

I will test and let you know. Thanks!

On 29 Mar 2006 09:02:18 -0800, das wrote:

 (snip)
>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?

Hi das,

Somewhat simpler than the suggestions Tom posted (and probably less
efficient, but still a major win over your present version) is the
following simple change:

select 'col1' as field,  col1 as val from table1
union ALL
select 'col2' as field,  col2 as val from table1
union ALL
select 'col3' as field,  col3 as val from table1

UNION without ALL will attempt to remove duplicates; with large result
sets, checking for duplicates can be a major performance killer. With
UNION ALL, you say "don't attempt to remove duplicates" - either because
you want them or (in this case) because you're sure there aren't any.

--
Hugo Kornelis, SQL Server MVP

that's a really good advice, didn't know what 'union all' meant all
these days.
I tried Thomas approach and it is much faster than before.
thanks a lot guys.

Been away for a while.  Here's a correction:

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 'col2' union all
select 'col3' 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

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:8izWf.1879$m35.157124@news20.bellglobal.com...
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

THANKS THOMAS!