SQL Query, how to avoid UNION for selecting multiple columns/rows

Hi.

I have a table that has 12 columns representing month. I need to write a query that returns those 12 columns as rows, so I can join on another table that then does some calculations.
The below reflects what I'm trying to do, but I was wondering if there is a smarter way of doing it. It brings back 12 rows and then I do a count based on the join.


select count(id) from anothertable a inner join  
	(
	select 1, field1 as M from mytable
	union
	select 2, field2 as M from mytable
	union
	select 3, field3 as M from mytable
	union
	select 4, field4 as M from mytable
	union
	select 5, field5 as M from mytable
	union
	select 6, field6 as M from mytable
	union
	select 7, field7 as M from mytable
	union
	select 8, field8 as M from mytable
	union
	select 9, field9 as M from mytable
	union
	select 10, field10 as M from mytable
	union
	select 11, field11 as M from mytable
	union
	select 12, field12 as M from mytable) as M on a.period = M.M

Open in new window

LVL 16
raybiesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
raybies said:
>>The below reflects what I'm trying to do, but I was wondering if there is a smarter way of doing it.

Given your current structure, UNION seems like a reasonable approach.

Perhaps, though, you would be better off normalizing your data, so that each month is on its own row, rather
than its own column.  The data's current denormalization is the root cause for why you are being forced to
use the UNION operator.
0
SharathData EngineerCommented:
you can replace UNION with UNION ALL for better performance.
0
raybiesAuthor Commented:
If you folk saw the state of the db, OMG... the original developers only knew about 1 data type, varchar.
I have recommended deleting the whole thing, but unfortunately I'm not able to change the schema.

I need the UNION as they're not unique.

If UNION is the most efficient way, I'll stick with it.

Thanks for the comments.



0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

SharathData EngineerCommented:
based on your select statements only, i mentioned UNION ALL over UNION.
0
SharathData EngineerCommented:
Provide sample set. We can work on to design better query. Instead of UNIONs you can write CASE statement. Its all depend on what exactly you are looking for?  Provide sample data from your anothertable and mytable and your desired output.
0
SujithData ArchitectCommented:
This is a direct conversion from Oracle to SQL Server, you may have to make a few syntax adjustments.
select count(id) from anothertable a inner join  
(
select field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12
from mytable CROSS JOIN 
(
select 1 as M
union
select 2 as M
union 
select 3 as M
union
select 4 as M
union 
select 5 as M
union
select 6 as M
union 
select 7 as M
union
select 8 as M
union 
select 9 as M
union
select 10 as M
union 
select 11 as M
union
select 12 as M
)
) as M on a.period = case when M.M = 1 then field1
                          when M.M = 2 then field2
			  when M.M = 3 then field3
			  when M.M = 4 then field4
			  when M.M = 5 then field5
			  when M.M = 6 then field6
			  when M.M = 7 then field7
			  when M.M = 8 then field8
			  when M.M = 9 then field9
			  when M.M = 10 then field10
			  when M.M = 11 then field11
			  when M.M = 12 then field12;

Open in new window

0
raybiesAuthor Commented:
Thanks again. I can't give you the real table or it's data (way too complicated), but the below simulates it.

The real table has some +60 columns 9 tables, and >900 000 000 records.

Thanks.
create table #anothertable(id int, period int, field1 int, field2 int, field3 int, field4 int, field5 int, field6 int, field7 int, field8 int, field9 int, field10 int, field11 int, field12 int)
create table #mytable(id int, field1 int, field2 int, field3 int, field4 int, field5 int, field6 int, field7 int, field8 int, field9 int, field10 int, field11 int, field12 int)
declare @c int,@i int, @s varchar(5000)
set @c = 1
set @i = 1
set @s = ''
while @c < 1000
begin 
	while @i < 13
	begin
		set @s = @s + 'insert into #anothertable (id, field'+ cast(@i as varchar(2)) +', period) values('+ cast(@c as varchar(2)) +',round(rand(convert(int, convert(varbinary, newid())))*10000,0),'+ cast(@i as varchar(2)) +')'
		set @i = @i + 1
	end
	set @c = @c + 1
	exec(@s)	
end
set @c = 1
set @i = 1
while @c < 10
begin  
	while @i < 13
	begin  
		set @s = @s + 'insert into #mytable (id, field'+ cast(@i as varchar(2)) +') values(round(rand(convert(int, convert(varbinary, newid())))*10000,0),'+ cast(@i as varchar(2)) +')'
		set @i = @i + 1
	end
	set @c = @c + 1
	exec(@s)
end
 
select (case 
		when M.M = 1 then sum(field1)
		when M.M = 2 then sum(field2)
		when M.M = 3 then sum(field3)
		when M.M = 4 then sum(field4)
		when M.M = 5 then sum(field5)
		when M.M = 6 then sum(field6)
		when M.M = 7 then sum(field7)
		when M.M = 8 then sum(field8)
		when M.M = 9 then sum(field9)
		when M.M = 10 then sum(field10)
		when M.M = 11 then sum(field11)
		when M.M = 12 then sum(field12)
		end) as MonthTotal, M.M
from #anothertable a inner join  
	(
	select field1 as M from #mytable
	union
	select field2 as M from #mytable
	union
	select field3 as M from #mytable
	union
	select field4 as M from #mytable
	union
	select field5 as M from #mytable
	union
	select field6 as M from #mytable
	union
	select field7 as M from #mytable
	union
	select field8 as M from #mytable
	union
	select field9 as M from #mytable
	union
	select field10 as M from #mytable
	union
	select field11 as M from #mytable
	union
	select field12 as M from #mytable) as M on a.period = M.M group by M.M order by M.M
 
 
drop table #anothertable
drop table #mytable

Open in new window

0
SujithData ArchitectCommented:
Try the above query.

The basic idea is instead of scanning the table 12 times, scan once and join the results with a dummy table of 12 rows. Basec on the month number take out the column that your require.
0
Mark WillsTopic AdvisorCommented:
If those unions are to get period (ie M.M  = period) then why not simply create #mytable vertically instead of horizontally ? Or is that just the example ? In which case, maybe a function or stored procedure to transpose those columns into rows. Which then leads onto have you played with PIVOT functions or suchlike on SQL2005 yet ?

And, maybe it is just the example, but, if you have the CASE there, then you already have your answer - the following should do exactly the same :

select (case
            when period = 1 then sum(field1)
            when period = 2 then sum(field2)
            when period = 3 then sum(field3)
            when period = 4 then sum(field4)
            when period = 5 then sum(field5)
            when period = 6 then sum(field6)
            when period = 7 then sum(field7)
            when period = 8 then sum(field8)
            when period = 9 then sum(field9)
            when period = 10 then sum(field10)
            when period = 11 then sum(field11)
            when period = 12 then sum(field12)
            end) as MonthTotal, period
from #anothertable
group by period
order by period

So, still not entirely sure why it has to be a series of unions to get the columns into rows - and not saying that it is not reasonable, it is just that there are a few different ways to achieve what you are looking for (maybe).



0
Snarf0001Commented:
Test for performance, but following is using the unpivot operator, which makes for much easier code to read:

-- sample JUST to unpivot the mytable values instead of unioning
select substring(upmt.field, 6, len(upmt.field)), M
from
      (select field1, field2, field3 from mytable) as mt
      unpivot(M for field in (field1, field2, field3)) as upmt
order by field, M


-- sample to to the join / count with mytable and anothertable example you gave:
select substring(upmt.field, 6, len(upmt.field)), M, count(a.id)
from
      (select field1, field2, field3 from mytable) as mt
      unpivot(M for field in (field1, field2, field3)) as upmt
      join anothertable a on upmt.M = a.period
group by substring(upmt.field, 6, len(upmt.field)), M
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
raybiesAuthor Commented:
Thanks for the comments.
It will take me a little while to work these in to my query, but I specially like the unpivot example.

I'll distribute some points.

Thanks folks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.