SQL Server 2000: dynamic SQL to build crosstab matrix

Fellow Experts,

I need a dynamic SQL statement to help me build a crosstab matrix in SQL Server 2000.

My firm's software product allows me to store any number of attributes and custom fields
for client work centers.  I am trying to build a sproc that will allow me to see a matrix of
attribute/custom field values.

So far, my sproc is able to create a temp table like this:

WC_Code           Attribute      Value
00002           Branch Type      Traditional
00002           Driveup Type      Back to Back
00002           Is Branch      Yes
00002           State                     OH
00002           Status                     Open
00004           Branch Type      In-Store
00004           Driveup Type      Other
00004           Is Branch      Yes
00004           State                      TX
00004           Status                     Closed

My sproc has these stored in a temp table #tmp.

I want the final output to generate one row per unique WC_Code, with columns for WC_Code
plus an additional column for each unique Attribute.  The column values would then be the
Value associated with that attribute for that work center.  Following the above example,
the results would be:


WC_Code        Branch Type      Driveup Type      Is Branch          State          Status
00002                      Traditional      Back to Back      Yes          OH          Open
00004                      In-Store                      Other                      Yes          TX          Closed


If I knew ahead of time what the attributes are, I could do this:

SELECT DISTINCT t1.WC_Code,
    (SELECT t2.Value FROM #tmp t2 WHERE t2.WC_Code = t1.WC_Code AND t2.Attribute = 'Branch Type') AS [Branch Type],
    (SELECT t2.Value FROM #tmp t2 WHERE t2.WC_Code = t1.WC_Code AND t2.Attribute = 'Driveup Type') AS [Driveup Type],
    (SELECT t2.Value FROM #tmp t2 WHERE t2.WC_Code = t1.WC_Code AND t2.Attribute = 'Is Branch') AS [Is Branch],
    (SELECT t2.Value FROM #tmp t2 WHERE t2.WC_Code = t1.WC_Code AND t2.Attribute = 'State') AS [State],
    (SELECT t2.Value FROM #tmp t2 WHERE t2.WC_Code = t1.WC_Code AND t2.Attribute = 'Status') AS [Status]
FROM #tmp t1
ORDER BY t1.WC_Code


The trouble is, of course, that I will not know how many attributes there will be ahead of time, nor will I
know their names necessarily, thus I need to build the SQL dynamically.

The sproc code so far:


ALTER PROCEDURE dbo.GrabWorkCenterAttribs
      @model_id uniqueidentifier
AS
      /* grab work center group values */
      SELECT w.work_center_code AS WC_Code, v.work_center_group_name AS Attribute,
            v.work_center_group_value AS Value
      INTO #tmp
      FROM dbo.work_center w INNER JOIN
            dbo.work_center_group_value v ON w.work_center_id = v.work_center_id
      WHERE w.model_id = @model_id

      /* grab custom field values */
      INSERT INTO #tmp (WC_Code, Attribute, Value)
      SELECT w.work_center_code, c.attribute_name, c.attribute_value
      FROM dbo.work_center w INNER JOIN
            dbo.custom_fields c ON w.work_center_id = c.context_id
      WHERE c.context_type = 'Work Center' AND w.model_id = @model_id

GO



Regards,

Patrick