Transposing SQL2000 Data

• porcrim

  SSC Eights!

  Points: 948

  More actions

  March 3, 2006 at 3:55 pm

  #93656

  I have a SQL 2000 table in which I am trying to transpose the data.  The layout is like this:

  create table SourceTable

  (

    UserID int null,

    ActivityID int null,

    RegistrationStatusID int null

  )

  insert into SourceTable values(1,2230,-1)

  insert into SourceTable values(1,2238,0)

  insert into SourceTable values(2,2230,2)

  insert into SourceTable values(2,2238,2)

  insert into SourceTable values(5,2230,-1)

  insert into SourceTable values(5,2238,2)

  I would like to tanspose the data so it is grouped by one userID per row with the other two columns adjacent.  The following code will work:

  SELECT UserID,

      SUM(CASE ActivityID WHEN 2230 THEN ActivityID ELSE 0 END) AS CourseA,

      SUM(CASE ActivityID WHEN 2230 THEN RegistrationStatusID ELSE 0 END) AS CourseAStatus,

      SUM(CASE ActivityID WHEN 2238 THEN ActivityID ELSE 0 END) AS CourseB,

      SUM(CASE ActivityID WHEN 2238 THEN RegistrationStatusID ELSE 0 END) AS CourseBStatus

  FROM SourceTable

  GROUP BY UserID

  But..

  what I need to do is avoid the hard-coded ActivityIDs as these items can change over time.  Select DISTINCT ActivityID FROM SourceTable will always give me the listing of ActivityIDs.  Do I need to create a cursor iterate through the SourceTable and retrieve the ActivityIDs?  I could then use that result in place of my hard-coded ActivityIDs.  Is there a way to do this without a cursor?

  Thanks,

  Matt

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  March 3, 2006 at 11:04 pm

  #624763

  Try this, I posted it a while ago, I was helping a person create a crosstab query, but he also wanted it dynamic.

  Reference this link when using/considering dynamic sql

  http://www.sommarskog.se/dynamic_sql.html

   

  -- Test Data Column Headers

  Create Table Temp1 (FieldID int identity, FormID int, FName varchar(50))

  -- Test Fields

  Insert into Temp1 (FormID, Fname)

  values (233, 'ContactName')

  Insert into Temp1 (FormID, Fname)

  values (233, 'EmailAddress')

  Insert into Temp1 (FormID, Fname)

  values (233, 'JobDescription')

  Insert into Temp1 (FormID, Fname)

  values (233, 'Structure')

  Insert into Temp1 (FormID, Fname)

  values (100, 'StreetNumber')

  Insert into Temp1 (FormID, Fname)

  values (100, 'StreetName')

  Insert into Temp1 (FormID, Fname)

  values (100, 'City')

  -- Test Data Values

  Create table Temp1Response (pk int identity, FieldID int, EventID int, UserID int, Response Varchar(100))

  -- Responses

  Insert into Temp1Response (FieldID, EventID, UserID, Response)

  Values(1, 1, 2, 'JoeSomebody')

  Insert into Temp1Response (FieldID, EventID, UserID, Response)

  Values(2, 1, 2, 'jSomebody@somewhere.com')

  Insert into Temp1Response (FieldID, EventID, UserID, Response)

  Values(3, 1, 2, 'Garbage Man')

  Insert into Temp1Response (FieldID, EventID, UserID, Response)

  Values(4, 1, 2, 'Partner')

  Insert into Temp1Response (FieldID, EventID, UserID, Response)

  Values(5, 2, 1, '100')

  Insert into Temp1Response (FieldID, EventID, UserID, Response)

  Values(6, 2, 1, 'Elm Street')

  Insert into Temp1Response (FieldID, EventID, UserID, Response)

  Values(7, 2, 1, 'Nowhere')

  DECLARE @SqlString nvarchar(4000)

  -- Set String varriable of select clause

  SELECT @SqlString = coalesce(@SqlString,'')  + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + FName + '],'

  FROM Temp1 A

  JOIN Temp1Response B on A.fieldId = b.FieldID

  -- Remove the last comma

  Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)

  -- Format string into a select statement

  SET @SqlString = 'SELECT UserID, ' + @SqlString + '

  FROM Temp1 A

  JOIN Temp1Response B on A.fieldId = b.FieldID

  Group by UserID'

  -- What is actually being executed

  select @SqlString

  Execute the string.

  exec sp_executeSql @SqlString

• paramind

  SSCrazy

  Points: 2140

  More actions

  March 5, 2006 at 2:18 am

  #624780

  I'd like to add on to this solution from a practical point of view:

  In a similar situation sometimes new productioncodeid's (~activity id's) were created. But that usually didn't happen that many times, that I would accept the EXEC sp_executeSQL performance hit.

  I simply created a trigger on the table in question (productioncodes/activityids) that recompiled my stored procedures (using a similar approach) when such a change in data occurred.

  Another way to achieve this, is to call the procedure by an intermediate procedure which first checks, whether the procedure creation has taken place prior to last data change in some table/some tables, e.g. activity id's. If so, the procedure is first dropped and dynamically recreated.

  In terms of application interface the procedure had the same 'parameter signature' which implied no need to change anything else. 

  
  _/_/_/ paramind _/_/_/

• paramind

  SSCrazy

  Points: 2140

  More actions

  March 5, 2006 at 2:24 am

  #624781

  another note: since a customer was not willing to switch to SQL 2005 and since I was not willing to do it the old way, I installed an instance of mssqlexpress (at no cost) side-by-side. Whenever I need some of the new features, e.g. PIVOT/UNPIVOT I pass the work to the SQL-Express instance and have that one do these things for me

  
  _/_/_/ paramind _/_/_/