Your question, your audience. Choose who sees your identity—and your question—with question security.

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.

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

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

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.

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

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.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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