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

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,

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.

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, 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;

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

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

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

Write Comment

By clicking you are agreeing to Experts Exchange's Terms of Use.

Featured Post

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …

Confronted with some SQL you don't know can be a daunting task.
It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as:
(+) as used in Oracle; *= =* as used in Sybase …

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…