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] 
