Optimizing SQL - Union

[das]

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

[Tom Moreau]

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" <Ad*******@gmail.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.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]

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]

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

[das]

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]

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" <to*@dont.spam.me.cips.ca> wrote in message
news:8i*******************@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" <Ad*******@gmail.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.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]

THANKS THOMAS!