Optimizing SQL - Union

**Tom Moreau** · Mar 29 '06, 05:45 PM

Re: Optimizing SQL - Union

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

https://www.pinpub.com

..
"das" <Adityanad@gmai l.com> wrote in message
news:1143651738 .146958.160520@ t31g2000cwb.goo glegroups.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

**das** · Mar 29 '06, 09:05 PM

Re: Optimizing SQL - Union

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

I will test and let you know. Thanks!

**Hugo Kornelis** · Mar 29 '06, 10:35 PM

Re: Optimizing SQL - Union

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

(snip)[color=blue]
>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?[/color]

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

**das** · Mar 30 '06, 02:55 PM

Re: Optimizing SQL - Union

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.

**Tom Moreau** · Apr 1 '06, 05:15 PM

Re: Optimizing SQL - Union

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

https://www.pinpub.com

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

https://www.pinpub.com

..
"das" <Adityanad@gmai l.com> wrote in message
news:1143651738 .146958.160520@ t31g2000cwb.goo glegroups.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

**das** · Apr 5 '06, 03:35 PM

Re: Optimizing SQL - Union

THANKS THOMAS!

Optimizing SQL - Union

Optimizing SQL - Union

Comment

Comment

Comment

Comment

Comment

Comment