Listing 1: Pivoting rows

-- Declare a table variable
DECLARE @DataTable TABLE 
    (
        MonthNumber TINYINT
        ,OrderCount SMALLINT
    )

-- fill the table with data
INSERT INTO @DataTable 
    (MonthNumber, OrderCount) 
    VALUES (1, 228)
INSERT INTO @DataTable 
    (MonthNumber, OrderCount) 
    VALUES (2, 250)
INSERT INTO @DataTable 
    (MonthNumber, OrderCount) 
    VALUES (3, 263)
INSERT INTO @DataTable 
    (MonthNumber, OrderCount) 
    VALUES (1, 328)
INSERT INTO @DataTable 
    (MonthNumber, OrderCount) 
    VALUES (3, 163)
INSERT INTO @DataTable 
    (MonthNumber, OrderCount) 
    VALUES (2, 350)

-- pivot rows in SQL 2000
SELECT
    SUM(CASE 
        WHEN MonthNumber = 1 THEN 
            OrderCount 
        ELSE 
            0 
        END) AS [1]
    ,SUM(CASE 
        WHEN MonthNumber = 2 THEN 
            OrderCount 
        ELSE 
            0 
        END) AS [2]
    ,SUM(CASE 
        WHEN MonthNumber = 3 THEN 
            OrderCount 
        ELSE 
            0 
        END) AS [3]
FROM @DataTable

-- pivot rows in SQL 2005
SELECT * 
FROM @DataTable
PIVOT (
        SUM(OrderCount)
        FOR MonthNumber 
            IN ([1],[2],[3])
      ) AS pvt


Listing 2: Unpivot in SQL 2005

-- Declare a table variable
DECLARE @DataTable TABLE 
    (
        [1]  SMALLINT
        ,[2] SMALLINT
        ,[3] SMALLINT
    )

-- fill the table with data
INSERT INTO @DataTable 
    ([1],[2],[3]) 
    VALUES (556,600,426)

-- pivot the columns into rows
SELECT * 
FROM @DataTable
UNPIVOT (
            OrderCount
            FOR MonthNumber 
                IN ([1],[2],[3])
        ) AS unpvt


Listing 3: OUTPUT

-- Create sample data
DECLARE @ProductTable TABLE
    (
        ProductID    INT
        ,ProductName VARCHAR(20)
        ,Price       DECIMAL(6,2)
    )

INSERT INTO @ProductTable 
    VALUES (
                1234
                ,'Left-handed Widget'
                ,19.95
           )
INSERT INTO @ProductTable 
    VALUES (
                1235
                ,'Right-handed Widget'
                ,14.95
           )

-- INSERT
DECLARE @InsertDetailsTable TABLE
    (
        NewProductID       INT
        ,NewProductName    VARCHAR(20)
        ,NewPrice          DECIMAL(6,2)
        ,ModifiedBy        SYSNAME
        ,ModifiedDate      DATETIME
    )

INSERT INTO @ProductTable
    (ProductID, ProductName, Price)
OUTPUT  inserted.ProductID
        ,inserted.ProductName
        ,inserted.Price
        ,SUSER_SNAME()
        ,GETDATE() 
        INTO @InsertDetailsTable
VALUES  (
            2345
            ,'Dual-purpose Widget'
            ,29.95
        )
SELECT * FROM @ProductTable
SELECT * FROM @InsertDetailsTable

-- UPDATE
DECLARE @UpdateDetailsTable TABLE
    (
        OldPrice	       DECIMAL(6,2)
        ,NewPrice	       DECIMAL(6,2)
        ,ModifiedBy      SYSNAME
        ,ModifiedDate    DATETIME
    )

UPDATE @ProductTable
SET Price = Price * 1.1
OUTPUT  deleted.Price
        ,inserted.Price
        ,SUSER_SNAME()
        ,GETDATE() 
        INTO @UpdateDetailsTable
WHERE ProductID = 1234

SELECT * FROM @ProductTable
SELECT * FROM @UpdateDetailsTable

-- DELETE
DECLARE @DeleteDetailsTable TABLE
    (
        OldProductID    INT
        ,ModifiedBy     SYSNAME
        ,ModifiedDate   DATETIME
    )

DELETE FROM @ProductTable
OUTPUT  deleted.ProductID
        ,SUSER_SNAME()
        ,GETDATE() 
        INTO @DeleteDetailsTable
WHERE ProductID = 1234

SELECT * FROM @ProductTable
SELECT * FROM @DeleteDetails