Dale Fraser Dale Fraser - 5 months ago 24
SQL Question

Group by non normalised Columns

Using SQL Server 2000 - I have an old database, and it's not normalised.

It has a bunch of columns like

memId
c1
c2
c3
c4
c5


These columns contain a number sample here

123
10
20
0
40
0

123
0
20
0
40
5


What I want is to extract the data grouped by the memId and column name like this

would come out as

memId col total
123 c1 10
123 c2 40
123 c4 80
123 c5 5


where the number is a sum for the group

I figured I could pull each time and union them all together, but was wondering if there is an easier way.

Answer

Sounds like you want to unpivot your results. One option for your database would be union all:

select memId, 'c1' as col, sum(c1) from yourtable group by memId, col
union all
select memId, 'c2' as col, sum(c2) from yourtable group by memId, col
union all
select memId, 'c3' as col, sum(c3) from yourtable group by memId, col
union all
select memId, 'c4' as col, sum(c4) from yourtable group by memId, col
union all
select memId, 'c5' as col, sum(c5) from yourtable group by memId, col