CREATE TABLE ##Books
(BookNumber INT,
BookName VARCHAR(50),
Author VARCHAR(50),
PublishDate DATETIME,
Price MONEY,
PurchaseDate DATETIME,
Quantity INT
)

INSERT INTO ##Books VALUES (1, 'Romeo and Juliet', 'Bob', '1/31/2008', 12, '1/1/2008', 2)
INSERT INTO ##Books VALUES (2, 'Venus and Mars', 'Phill', '1/31/2008', 32, '4/5/2008', 4)
INSERT INTO ##Books VALUES (3, 'Awake', 'Phill', '4/15/2008', 89, '12/24/2005', 5)
INSERT INTO ##Books VALUES (1, 'Romeo and Juliet', 'Bob', '1/31/2008', 12, '1/1/2008', 77)
INSERT INTO ##Books VALUES (3, 'Awake', 'Phill', '4/15/2008', 89, '11/1/2008', 32)
INSERT INTO ##Books VALUES (4 , 'The End ','Ken', '11/12/2008', 78, '4/5/2008', 3)
INSERT INTO ##Books VALUES (5, 'Venus and Mars', 'Phill', '6/3/2008', 76, '3/2/2008', 56)
INSERT INTO ##Books VALUES (5, 'Venus and Mars', 'Phill', '6/3/2008', 76, '11/21/2008', 9)
INSERT INTO ##Books VALUES (7, 'Awake', 'Phill', '12/15/2008', 43, '1/1/2008', 75)
INSERT INTO ##Books VALUES (5, 'Venus and Mars', 'Phill', '6/3/2008', 76, '5/5/2008', 9)
INSERT INTO ##Books VALUES (3, 'Awake', 'Phill', '4/15/2008', 89, '5/5/2008', 21)
INSERT INTO ##Books VALUES (7, 'Awake', 'Phill', '12/15/2008', 43, '3/2/2008', 3)
INSERT INTO ##Books VALUES (5, 'Venus and Mars ', 'Phill', '6/3/2008', 76, '1/1/2008', 3)
INSERT INTO ##Books VALUES (6, 'Absolute Power', 'Ken', '6/14/2008', 5, '3/2/2008', 2)
INSERT INTO ##Books VALUES (4, 'The End', 'Ken', '11/12/2008', 78, '3/2/2008', 2)


--PIVOTED RESULTS
--=============
SELECT * FROM ##Books

SELECT PurchaseMonth, [Bob] as 'Bob''s Earnings', [Phill] AS 'Phill''s Earnings', [Ken] AS 'Ken''s Earnings'
FROM (SELECT DATENAME(MONTH,PurchaseDate) AS 'PurchaseMonth', Author, (price * quantity) AS Earnings
FROM ##Books ) AS st
PIVOT (
SUM (Earnings)
FOR Author IN ([Bob], [Phill], [Ken])
) AS pt

Give the following results
/*
PurchaseMonth Bob's Earnings Phill's Earning Ken's Earnings
April NULL 128.00 234.00
December NULL 445.00 NULL
January 948.00 3453.00 NULL
March NULL 4385.00 166.00
May NULL 2553.00 NULL
November NULL 3532.00 NULL
*/


/*I Want to modify the script to create a stored procedure that is flexible enough to be reused on different tables.
I want to be able to pass:
- the source table
- the column of that table to pivot into columns
- the column to be used as row headings
- the column whose data must show summarised under the pivoted columns
- the aggregate function to be used to summarise the data under the pivoted columns


And be able to to order the Pivot Column and Heading Column (asc or desc).
*/