Pivot, Store Procedure and passing PCREATarameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot, Store Procedure and passing PCREATarameters

Author

Topic

Littleterry
Starting Member

9 Posts

Posted - 2008-08-31 : 02:00:51

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).

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-31 : 02:06:08

do a search on Dynamic Cross Tab query and Dynamic Pivot Sql in google and you should get a bunch of results that'll point you in the correct direction.

Littleterry
Starting Member

9 Posts

Posted - 2008-08-31 : 02:08:35

Sweet thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-31 : 06:09:26

www.mssqltips.com/tip.asp?tip=937

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources