Technical Article

Generate CROSS-TAB Scripts Automaticly !

,

Need to Transform a table with Summaries to be plugged into EXCEL or place a table of info in one row. This is cool and has been a real time saver!!!

Simply save as a Template file in your favorite template folder. In Query Analyzer go to Menu Tools... Options... General... Note the path of your "Template file Directory" Save as a *.tql file.(see also "Using Templates in SQL Query Analyzer" in SQL Books Online)
OR
Simply play with the default generated script. Listed after the template.
This is a templated supported in SQL Server 2000

/***  Template to Create CROSS-TAB Select Statement ***
    *
    *   Date:       4/26/2002
    *   Author:     Dan Collier     
    *Email:      dcollier@kha.com
    *              
    *   Description:  Template to take rows with 
    *        common column data or grouping data
    *        and auto generate script to Transform them 
    *        into Column names and
    *        grouped aggregates.
    *
    *   Db Engine:  SQL Server 2000 
    *   Client: SQL Query Analyzer
    */
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)

/* Start with PREFIX Select statement */SELECT @SQL = N'SELECT <Row_Header , sysname, [titles].[pub_id]> '+char(13)+','

/* Transform Cross Tab. Group Row values to Columns. */SELECT @SQL = @SQL + N'ISNULL(<Aggregate_Function, sysname, SUM>(CASE WHEN <Field for Column Headers, sysname, [titles].[type]>='''
  +RTRIM(<Field for Column Headers, sysname, [titles].[type]>)+''''+char(13)+
  '    THEN ISNULL([titles].[price],0) END),0) AS <Aggregate Function, sysname, SUM>_'+
  +RTRIM(<Field for Column Headers, sysname, [titles].[type]>)+char(13)+','
  <FROM_Tables_Joins, char, FROM [pubs].[dbo].[titles]>
  WHERE LEN(RTRIM(<Field for Column Headers, sysname, [titles].[type]>)) > 0
  GROUP BY <Field for Column Headers, sysname, [titles].[type]>

/* Remove last comma and return */SELECT @SQL = LEFT(@SQL,LEN(@SQL)-2)+char(13) 

/* Add SUFFIX Statements */SELECT @SQL = @SQL + N'<FROM_Tables_Joins, char, FROM [pubs].[dbo].[titles]>'+char(13)+
  'GROUP BY <Row_Header , sysname, [titles].[pub_id]>'

/* Generate the T-SQL statement */SELECT @SQL 

-- *** The defaults setup this statement >>>
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)

/* Start with PREFIX Select statement */SELECT @SQL = N'SELECT [titles].[pub_id] '+char(13)+','

/* Transform Cross Tab. Group Row values to Columns. */SELECT @SQL = @SQL + N'ISNULL(SUM(CASE WHEN [titles].[type]='''
  +RTRIM([titles].[type])+''''+char(13)+
  '    THEN ISNULL([titles].[price],0) END),0) AS SUM_'+
  +RTRIM([titles].[type])+char(13)+','
  FROM [pubs].[dbo].[titles]
  WHERE LEN(RTRIM([titles].[type])) > 0
  GROUP BY [titles].[type]

/* Remove last comma and return */SELECT @SQL = LEFT(@SQL,LEN(@SQL)-2)+char(13) 

/* Add SUFFIX Statements */SELECT @SQL = @SQL + N'FROM [pubs].[dbo].[titles]'+char(13)+
  'GROUP BY [titles].[pub_id]'

/* Generate the T-SQL statement */SELECT @SQL 

-- *** Output of the Generated Script >>>

SELECT [titles].[pub_id] 
,ISNULL(SUM(CASE WHEN [titles].[type]='business'
    THEN ISNULL([titles].[price],0) END),0) AS SUM_business
,ISNULL(SUM(CASE WHEN [titles].[type]='mod_cook'
    THEN ISNULL([titles].[price],0) END),0) AS SUM_mod_cook
,ISNULL(SUM(CASE WHEN [titles].[type]='popular_comp'
    THEN ISNULL([titles].[price],0) END),0) AS SUM_popular_comp
,ISNULL(SUM(CASE WHEN [titles].[type]='psychology'
    THEN ISNULL([titles].[price],0) END),0) AS SUM_psychology
,ISNULL(SUM(CASE WHEN [titles].[type]='trad_cook'
    THEN ISNULL([titles].[price],0) END),0) AS SUM_trad_cook
,ISNULL(SUM(CASE WHEN [titles].[type]='UNDECIDED'
    THEN ISNULL([titles].[price],0) END),0) AS SUM_UNDECIDED
FROM [pubs].[dbo].[titles]
GROUP BY [titles].[pub_id]

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating