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

Link-Tipp zum Thema "ASP.net": www.4guysfromrolla.com/

Umfangreiche Site rund um ASP.net. Guter Startpunkt für dieses Thema