Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2277
  • Last Modified:

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

0
raybies
Asked:
raybies
  • 3
  • 3
  • 2
  • +3
3 Solutions
 
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
Technology Partners: We Want Your Opinion!

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!

 
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
 
sujith80Commented:
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
 
sujith80Commented:
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now