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

Answer 2

It seems I misunderstood your point, oops. You are migrating the information to another table?

But first, a note about my use of the term normalization

First Normal Form
 - Eliminate repeating groups in individual tables.
 - Create a separate table for each set of related data.
 - Identify each set of related data with a primary key.

So clearly I was wrong about the normalization. Ouch. Good thing I'm still young, eh? :/

I spent some time reevaluating this (a fatal mistake, no?) and also what the limitations of SQL Server 2000 are. I found a useful compilation of the SQL Server 2000 manual available on redware - SQL SERVER Handbook.

When it comes to table expressions...the only thing that appears was subqueriesand views, though no true Ranking function is available (thankfully, you can create Functions).

While you could add a value through some kind of cursive, why?

• You are still going to be parsing the table at least N x #Columns to be unpivoted. No complicated pivot (which did not exist anyways) required.
• Instead of using any kind of expensive cursive, SELECT 'C1' is simple and easily modified to the rows.
• You want to concatenate the tables, so the simplest method still is the UNION ALL.
• You can run the GROUP BY once after concatenation. Simple and elegant.

SOLUTION:

SELECT memID
     , Col
     , SUM(C1) AS Count 
FROM (
      SELECT 'C1' AS [Col], memID, C1 FROM  #Test2
      UNION ALL SELECT 'C2' AS [Col], memID, C2 FROM #Test2
      UNION ALL SELECT 'C3' AS [Col], memID, C3 FROM #Test2
      UNION ALL SELECT 'C4' AS [Col], memID, C4 FROM #Test2
      UNION ALL SELECT 'C5' AS [Col], memID, C5 FROM #Test2 ) AS A
GROUP BY memID, Col
ORDER BY memID ASC, Col ASC

Source Table:

CREATE TABLE #Test2 (memID INT, C1 INT, C2 INT, C3 INT, C4 INT, C5 INT)
INSERT INTO #Test2 (memId, C1, C2, C3, C4, C5)
VALUES (123, 10, 20, 0, 40, 0)
     , (123, 0, 20, 0, 40, 5)
     , (122, 5, 20, 10, 15, 0)
     , (122, 5, 0, 0, 0, 60)
     , (155, 10, 0, 0, 10, 10)
     , (155, 0, 0, 0, 50, 50)

Results:

memID   Col Count
122     C1  10
122     C2  20
122     C3  10
122     C4  15
122     C5  60
123     C1  10
123     C2  40
123     C3  0
123     C4  80
123     C5  5
155     C1  10
155     C2  0
155     C3  0
155     C4  60
155     C5  60

So I think your initial idea was right on. Cheers.