• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2884
  • Last Modified:

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
0
Patrick Matthews
Asked:
Patrick Matthews
  • 7
  • 5
  • 4
  • +3
3 Solutions
 
harfangCommented:
I would suggest a simple cross-tab, like this:

    TRANSFORM First(Value)
    SELECT WC_Code
    FROM TableOfAttributes
    GROUP BY WC_Code
    PIVOT Attribute

But for some reason, MS-SQL does not have any form of cross-tabs natively. I even found this very funny quotation at:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/accessmigration.mspx#E5CAE

"SQL Server does not support the Access TRANSFORM keyword for crosstab queries. The best way to re-create your crosstab queries in SQL Server is to use a third-party tool." (They even list one right there...)

Wow!
(°v°)
0
 
Patrick MatthewsAuthor Commented:
Markus,

That is amusing, that Microsoft refers people to third party tools :)

Unfortunately, this has to be done in SQL Server 2000, so Access isn't going to help.  (SQL Server
2005 does have a new PIVOT operator, but again, this has to be in 2000...)

Regards,

Patrick
0
 
puppydogbuddyCommented:
Hi Patrick and Markus,
Don't know if this will help or not, but the reference sources listed indicate that the equivalent to the Transform statement is the With Rollup, With Cube. on Select statements.  The syntax is privided in the second reference source.

http://www.microsoft.com/technet/prodtechnol/sbs/2000/reskit/sbrk0025.mspx?mfr=true
Table 25.2 Differences Between SQL Server 2000 and Access Syntax
                      Access                                                   SQL Server
TRANSFORM(SELECT statement)      WITH ROLLUP, WITH CUBE on SELECT statements
PIVOT      Not applicable


http://www.microsoft.com/technet/prodtechnol/sbs/2000/reskit/sbrk0025.mspx?mfr=true
Crosstabs are used for summary reports. An Access CROSSTAB can be implemented as a Transact-SQL SELECT statement in a SQL script, a stored procedure, or a view. The data join is re-executed each time a query is issued, ensuring that the latest data is always used.Depending on the application, it might be appropriate to store data from the crosstab as a temporary table (see MAKE TABLE). The temporary table requires fewer resources but offers only a snapshot of the data at the time the temporary table is created.

An Access MAKE TABLE can be implemented as a Transact-SQL CREATE TABLE statement in a Transact-SQL script or stored procedure. The syntax follows:SELECT [ ALL | DISTINCT ][ {TOP integer | TOP integer PERCENT} [ WITH TIES] ] <select_list>[ INTO new_table ][ FROM {<table_source>} [,&n] ][ WHERE <search_condition> ][ GROUP BY [ALL] group_by_expression [,&n] [ WITH { CUBE | ROLLUP } ]CREATE TABLE mytable (low int, high int)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
nmcdermaidCommented:
SQL Server is not a presentation platform (unless you use reporting services). However MS Access contains a presentation layer. Thats the difference.

A crosstab is of very little use in internally processing data. Its generally only useful to an end user.

What will you actually be presenting this data in? It is highly unlikley that you are going to be pressenting it in SQL Server client tools. Do the crosstab in whatever you are presenting it in.
0
 
dqmqCommented:
After building your temp table, try this:


declare @sql varchar(8000)
set @sql = 'Select WC_Code '

select @sql=@sql + ', max(case attribute when ''' +  Attribute + ''' then value else '''' end) as [' + Attribute + '] '  
from #tmp

set @sql = @sql + 'from #tmp group by wc_code order by wc_code'
productname'

exec (@sql)
0
 
dqmqCommented:
Oops...this is better:

declare @sql varchar(8000)
--build head
set @sql = 'Select WC_Code '
--append body
select @sql=@sql + ', max(case attribute when ''' +  Attribute + ''' then value else '''' end) as [' + Attribute + '] ' from #tmp
--append tail  
set @sql = @sql + 'from #tmp group by wc_code order by wc_code'
 --run sql
exec (@sql)
0
 
dbbishopCommented:
SELECT pri.WC_Code, bt.Value AS [Branch Type], dt.Value AS [Driveup Type], ib.Value AS [Is Branch], st.Value AS State, sts.Value AS Status
FROM myTable pri
LEFT JOIN myTable bt ON pri.WC_Code = bt.WC_Code AND bt.Attribute = 'Branch Type'
LEFT JOIN myTable dt ON pri.WC_Code = dt.WC_Code AND dt.Attribute = 'Driveup Type'
LEFT JOIN myTable ib ON pri.WC_Code = ib.WC_Code AND ib.Attribute = 'Is Branch'
LEFT JOIN myTable st ON pri.WC_Code = st.WC_Code AND st.Attribute = 'State'
LEFT JOIN myTable sts ON pri.WC_Code = sts.WC_Code AND sts.Attribute = 'Status'
GROUP BY pri.wc_code, bt.value, dt.value, ib.value, st.value, sts.value
ORDER BY pri.WC_Code
0
 
dbbishopCommented:
Sorry, I just read your statement: "If I knew ahead of time what the attributes are, I could do this..."

I would read distinct Attribute values into a cursor, then buold the SELECT statement and add the LEFT JOINs.

This uses the ****EVIL****, ****DEARDED****, **NEVER TO BE USED** cursor, but should work:
note that it does not control the order of the columns.

SET @Prefix = 'p'
SET @PrefixIdx = 1
SET @SQLSelect = 'SELECT p0.WC_Code '
SET @SQLJoin = ''
SET @SQLGroup = 'GROUP BY p0.WC_Code'

DECLARE csrAttribute CURSOR FOR SELECT DISTINCT Attribute FROM myTable
OPEN csrAttribute

FETCH NEXT FROM csrAttribute INTO @Attribute
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tblPrefix = @Prefix + CAST(@PrefixIDX AS VARCHAR(2))
    SET @SQLSelect = @SQLSelect + ', ' + @tblPrefix + '.Value AS [' + @Attribute + ']'
    SET @SQLJoin = @SQLJoin + ' LEFT JOIN myTable ' + @tblPrefix + ' ON p0.WC_Code = ' + @tblPrefix + '.WC_Code AND ' + @tblPrefix + '.Attribute = ' + '''' + @Attribute + ''''
    SET @SQLGroup = @SQLGroup + ', ' + @tblPrefix + ',Value'

    SET @PrefixIDX = @PrefixIDX + 1
    FETCH NEXT FROM csrAttribute INTO @Attribute
END
SET @SQLSelect = @SQLSelect + ' FROM myTable p0 ' + @SQLJoin + ' '+ @SQLGroup + ' ORDER BY p0.WC_Code '
PRINT @SQLSelect

CLOSE csrAttribute
DEALLOCATE csrAttribute
0
 
dbbishopCommented:
Forgot to paste the DECLARE statements. Also, you'll need to change myTable to your actual table name:

DECLARE @Prefix VARCHAR(1), @PrefixIDX TINYINT, @tblPrefix VARCHAR(4), @SQLSelect VARCHAR(2000), @SQLJoin VARCHAR(2000), @SQLGroup VARCHAR(2000), @Attribute VARCHAR(50)
0
 
dbbishopCommented:
Also, noticed I've got a comma in front of Value (,Value) in the GROUP BY string buiolder. Change that to a '.':

   SET @SQLGroup = @SQLGroup + ', ' + @tblPrefix + '.Value'


0
 
Patrick MatthewsAuthor Commented:
Dennis and db,

Thanks for your comments.  I shall try those out, and let you know how it goes.  While I try to
avoid cursors, in this case it should be a big deal; the largest I ever expect my #tmp table to
get to is maybe 100k - 150k rows, with not more than a couple dozen unique attributes
going into the cursor.

nc: yes, I hear you on all points.  I would have preferred to do this in Access as I have much more
experience there, but the eventual end users of the data will not be able to hook up Access to
the SQL Server 2000 database.

Regards,

Patrick
0
 
dbbishopCommented:
Patrick,

I know what you mean about cursors, but, the same could be said about dynamic SQL. However, although either 'MIGHT' be unavoidable most of the time, sometimes they just make good sense. In this case, it really doesn't matter how many rows the temp table has, it is how many unique 'Attribute' values it contains.

Doug
0
 
Patrick MatthewsAuthor Commented:
Doug,

Sorry, that should have read...

"While I try to avoid cursors, in this case it should NOT be a big deal; ..."

Regards,

Patrick
0
 
dqmqCommented:
You don't need a cursor, just dynamic SQL :>).  Let me reiterate:

declare @sql varchar(8000)
set @sql = 'Select WC_Code '
select @sql=@sql + ', max(case attribute when ''' +  Attribute + ''' then value else '''' end) as [' + Attribute + '] '
   from #tmp
set @sql = @sql + 'from #tmp group by wc_code order by wc_code'
exec (@sql)
0
 
dbbishopCommented:
Dennis is right. I should have looked closer at his code :-)
0
 
Patrick MatthewsAuthor Commented:
Dennis,

I tried implementing your suggestion, ending up with this:




ALTER PROCEDURE dbo.GrabWorkCenterAttribs
      @model_id uniqueidentifier
AS
      IF EXISTS (SELECT * from sysobjects WHERE id = OBJECT_ID('[dbo].[tmp]') AND
            sysstat & 0xf = 3) DROP TABLE [dbo].[tmp]

      /* 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 dbo.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 dbo.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

      DECLARE @sql varchar(8000)

      SET @sql = 'SELECT WC_Code'
      SELECT @sql = @sql + ', MAX(CASE Attribute WHEN ''' +  Attribute + ''' THEN RTRIM(Value) ELSE '''' END) AS [' + Attribute + ']' FROM dbo.tmp GROUP BY Attribute
      SET @sql = @sql + ' FROM dbo.tmp GROUP BY WC_Code ORDER BY WC_Code'

      EXEC @sql

      DROP TABLE dbo.tmp      

GO



When I try to run that, I always get an error message:

Server: Msg 203, Level 16, State 2, Procedure GrabWorkCenterAttribs, Line 28
The name '<sql statement here>' is not a valid identifier

(Line 28 is EXEC @sql)

If I set up a batch to run the SQL statement that is actually generated, it runs fine.  I suspect I am
missing something here with the scope, but I cannot figure out what.

Regards,

Patrick
0
 
nmcdermaidCommented:
>> but the eventual end users of the data will not be able to hook up Access to the SQL Server 2000 database

So what will they be using to view the data?... surely not SQL Server client tools.

0
 
dbbishopCommented:
EXEC (@SQL)
0
 
dqmqCommented:
That's right, add the parens and you will be happy.

You can also replace the temp table with a view:

CREATE VIEW dbo.WorkCenterAttribs
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
      FROM dbo.work_center w INNER JOIN
            dbo.work_center_group_value v ON w.work_center_id = v.work_center_id
UNION ALL
/* grab custom field values */
      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'
GO


ALTER PROCEDURE dbo.GrabWorkCenterAttribs
      @model_id uniqueidentifier
AS
      DECLARE @sql varchar(8000)

      SET @sql = 'SELECT WC_Code'
      SELECT @sql = @sql + ', MAX(CASE Attribute WHEN ''' +  Attribute + ''' THEN RTRIM(Value) ELSE '''' END) AS [' + Attribute + ']'
FROM dbo.WorkCenterAttribs  
WHERE model_id = @model_id
GROUP BY Attribute

      SET @sql = @sql + ' FROM dbo.WorkCenterAttribs  GROUP BY WC_Code ORDER BY WC_Code where model_id=''' + cast(@model_id as varchar(36)) + ''''

    EXEC (@sql)

GO
0
 
Patrick MatthewsAuthor Commented:
Dennis and Doug,

Thank you gents!  I feel very silly now for missing those parentheses :)

nmcdermaid: for now, it is most likely that my client's DBA will run that for them from time
to time.  Eventually, I expect that we will be extending the functionality of our application
to allow for the ability for designated application end users to run it themselves.

Regards,

Patrick
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now