IF EXISTS (SELECT name FROM sysobjects
                   where type = "P"
               AND name = 'sp_Transform')
      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
   last change: march 2000
   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

      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)
AS
DECLARE @TransformPart  nvarchar(4000)
DECLARE @SQLColRetrieval nvarchar(4000)
DECLARE @SQLSelectIntro     nvarchar(4000)
DECLARE @SQLSelectFinal nvarchar(4000)
IF ( CHARINDEX(@Aggregate_Function, 'SUM|COUNT|MAX|MIN|AVG|STDEV|VAR|VARP|STDEVP', 1) = 0 )
  BEGIN RAISERROR ('Invalid aggregate function', 10, 1) END
ELSE
BEGIN
  SELECT @SQLSelectIntro = 'SELECT CASE WHEN (GROUPING('  +
          QUOTENAME(@Select_Column) +
     ') = 1) THEN ''Total'' ELSE ' +
     QUOTENAME(@Select_Column) +
     ' END As ' + QUOTENAME(@Select_Column) +
     ', '
  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'

  EXEC sp_executesql @SQLColRetrieval,
                            N'@TransformPart nvarchar(4000) OUTPUT',
                            @TransformPart OUTPUT
  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'
  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 "C#.net": www.codeproject.com/

Sehr umfangreiche Site zu den Themen C++, C# und dot.Net Entwicklung