Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: Pivot table for non aggregate table

    I have following table.
    W_id: Work ID
    dayofweek: Day Of week
    clock: Time
    Emp_id: Employee ID

    W_id dayofweek clock Emp_id
    13 Monday 1 t106101
    0 Monday 2 t106101
    25 Monday 3 t106101

    0 Tuesday 1 t106101
    24 Tuesday 2 t106101
    0 Tuesday 3 t106101

    I'm trying to display above table as following. Like a Schedule.

    Clock |Monday|Tuesday
    1 |13 |0
    2 |0 |24
    3 |25 |0

    Any help would be greatly appreciated. I did try to do my HW but couldn't figure it out with pivot table.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    A very simple explanation can be found here:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/24ba54fc-98f7-4d35-8881-b5158aac1d66.htm

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831

    Pivot tables 101 - example

    CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int)
    GO
    INSERT INTO pvt VALUES (1,4,3,5,4,4)
    INSERT INTO pvt VALUES (2,4,1,5,5,5)
    INSERT INTO pvt VALUES (3,4,3,5,4,4)
    INSERT INTO pvt VALUES (4,4,2,5,5,4)
    INSERT INTO pvt VALUES (5,5,1,5,5,5)
    GO

    CREATE TABLE Purchasing (PurchaseOrderHeader varchar(55),PurchaseOrderID int, EmployeeID int, VendorID int)
    INSERT INTO Purchasing VALUES ('Books',122211,164,1)
    INSERT INTO Purchasing VALUES ('Toys',122212,231,2)
    INSERT INTO Purchasing VALUES ('Toys',122213,233,3)
    INSERT INTO Purchasing VALUES ('Paint Supplies',122214,231,4)
    INSERT INTO Purchasing VALUES ('Food',122215,164,5)
    INSERT INTO Purchasing VALUES ('Food',122255,223,6)
    INSERT INTO Purchasing VALUES ('Food',122216,164,7)
    INSERT INTO Purchasing VALUES ('Hobby',122217,223,8)
    INSERT INTO Purchasing VALUES ('Hobby',122218,198,9)
    INSERT INTO Purchasing VALUES ('Hobby',122219,233,10)


    --Unpivot the table.
    SELECT PurchaseOrderHeader as Description, [164] AS Sue, [198] AS Bob, [223] AS Ronan, [231] AS Tim, [233] AS Shane
    FROM
    (SELECT PurchaseOrderHeader,PurchaseOrderID, EmployeeID
    FROM Purchasing) p
    PIVOT
    (
    COUNT (PurchaseOrderID)
    FOR EmployeeID IN
    ( [164], [198], [223], [231], [233] )
    ) AS pvt
    ORDER BY PurchaseOrderHeader

    DROP TABLE pvt
    DROP TABLE Purchasing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •