Group by non normalised Columns

Question

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 1

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

• SQL Fiddle Demo