IF OBJECT_ID('sp_Transform', 'P') IS NOT NULL
DROP PROCEDURE sp_Transform
GO
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
Input parameters:
@Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@Aggregate_Column
name of column for aggregate
@TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@Select_Column
Column for first column in result table
for this column row values are displayed
@Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @Aggregate_Column='Sales', @TableOrViewName='aTable',
@Select_Column='Product', @Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
-------+----------+----------+---------+---------
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
-------+----------+----------+---------+---------
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@Aggregate_Function nvarchar(30) = 'SUM',
@Aggregate_Column nvarchar(255),
@TableOrView_Name nvarchar(255),
@Select_Column nvarchar(255),
@Pivot_Column nvarchar(255),
@DEBUG bit = 0
AS
SET NOCOUNT ON
DECLARE @TransformPart nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro nvarchar(4000)
DECLARE @SQLSelectFinal nvarchar(4000)
IF @Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1, @Aggregate_Function) END
ELSE
BEGIN
SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@Select_Column) +
', '
IF @DEBUG = 1 PRINT @sqlselectintro
SET @SQLColRetrieval =
N'SELECT @TransformPart = CASE WHEN @TransformPart IS NULL THEN ' +
N'''' + @Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@Pivot_Column AS varchar(255))) +
N' AS VARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(@Pivot_Column) +
N' AS NVarchar(255)) + '''''' THEN ' + @Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Column AS varchar(255))) +
N') ELSE @TransformPart + '', ' + @Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@Pivot_Column) +
N' AS nVARCHAR(255)) WHEN '''''' + CAST(' +
QUOTENAME(CAST(@Pivot_Column AS varchar(255))) +
N' AS nVARCHAR(255)) + '''''' THEN ' +
@Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@Pivot_Column AS varchar(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@Pivot_Column AS varchar(255))) +
N' FROM ' + @TableOrView_Name + ') SelInner'
IF @DEBUG = 1 PRINT @SQLColRetrieval
EXEC sp_executesql @SQLColRetrieval,
N'@TransformPart nvarchar(4000) OUTPUT',
@TransformPart OUTPUT
IF @DEBUG = 1 PRINT @TransformPart
SET @SQLSelectFinal =
N', ' + @Aggregate_Function + N'(' +
CAST(@Aggregate_Column AS varchar(255)) +
N') As Total FROM ' + @TableOrView_Name + N' GROUP BY ' +
@Select_Column + N' WITH CUBE'
IF @DEBUG = 1 PRINT @SQLSelectFinal
EXEC (@SQLSelectIntro + @TransformPart + @SQLSelectFinal)
END
GO
/*
To test the preceding stored procedure uncomment the
following section
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'aTable')
DROP TABLE aTable
--GO
CREATE TABLE aTable(Product varchar(2), Salesman varchar(2), Sales int)
INSERT INTO aTable VALUES('P1', 'Sa', 12)
INSERT INTO aTable VALUES('P2', 'Sb', 10)
INSERT INTO aTable VALUES('P2', 'Sb', 3)
INSERT INTO aTable VALUES('P3', 'Sa', 12)
INSERT INTO aTable VALUES('P1', 'Sc', 8)
INSERT INTO aTable VALUES('P3', 'Sa', 1)
INSERT INTO aTable VALUES('P2', 'Sa', NULL)
EXEC sp_Transform @Aggregate_Column='Sales', @TableOrView_Name='aTable',
@Select_Column='Product', @Pivot_Column='Salesman'
*/
-- Example from Northwind Table
-- USE NORTHWIND
-- GO
-- EXEC sp_transform 'sum', 'unitprice', 'products', 'productname', 'CategoryID'
-- GO