0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dale Fraser
  • 4,623
  • 7
  • 39
  • 76
  • Its a SQL 2000 db and unpivot doesnt seem to be supported – Dale Fraser Jul 06 '16 at 00:12
  • 6
    People still use SQL2000?! My condolences... – DavidG Jul 06 '16 at 00:13
  • Thank you for your kind thoughts, I did open with "I have an old database" – Dale Fraser Jul 06 '16 at 00:28
  • 4
    I'm not sure "old database" quite describes a product that came out 16 years ago and hasn't even been supported for almost a decade! But how about a manual pivot, something like this http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000 – DavidG Jul 06 '16 at 00:33
  • Im looking to take columns to rows not the other way – Dale Fraser Jul 06 '16 at 00:43
  • you don't need the unpivot command to unpivot.... – Mitch Wheat Jul 06 '16 at 01:43
  • There are plenty of ways to do it in a newer DBMS. In an older one, a union statement is probably your best option. Without applies (which I don't think exist in SQL2000) and `UNPIVOT`, your options are pretty limited. – ZLK Jul 06 '16 at 05:10
  • It's not just that Sql Server 2000 is "old'. Far more concerning is that it's old enough to be **way** past end of life. It no longer gets any patches, even for critical security issues, and hasn't for some time now. Continuing to use it is highly irresponsible. Upgrading this server is job 1. – Joel Coehoorn Jul 23 '16 at 18:57

2 Answers2

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
sgeddes
  • 62,311
  • 6
  • 61
  • 83
-1

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.

clifton_h
  • 1,298
  • 8
  • 10
  • I already have the data in that format, I need it such that each column becomes a row as per the example I provided. – Dale Fraser Jul 07 '16 at 00:28
  • @DaleFraser see my updated response. Thanks for the feedback. Definitely do not want to insist on bad knowledge! – clifton_h Jul 07 '16 at 09:13