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

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.

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

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.

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, field12from mytable CROSS JOIN (select 1 as Munionselect 2 as Munion select 3 as Munionselect 4 as Munion select 5 as Munionselect 6 as Munion select 7 as Munionselect 8 as Munion select 9 as Munionselect 10 as Munion select 11 as Munionselect 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;

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 = 1set @i = 1set @s = ''while @c < 1000begin 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) endset @c = 1set @i = 1while @c < 10begin 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)endselect (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.Mfrom #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.Mdrop table #anothertabledrop table #mytable

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.

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).

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

>>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.