Pivot Table - Example #2

CREATE TABLE #tempRecords
(
Record int,
BookNo int,
BookValue varchar(50)
)

INSERT INTO #tempRecords VALUES (1,1,'One')
INSERT INTO #tempRecords VALUES (1,2,'Two')
INSERT INTO #tempRecords VALUES (1,3,'Three')
INSERT INTO #tempRecords VALUES (1,4,'Four')

INSERT INTO #tempRecords VALUES (2,1,'One')
INSERT INTO #tempRecords VALUES (2,2,'Two')
INSERT INTO #tempRecords VALUES (2,3,'Three')
INSERT INTO #tempRecords VALUES (2,7,'Seven')
INSERT INTO #tempRecords VALUES (2,8,'Eight')

INSERT INTO #tempRecords VALUES (3,9,'Nine')

INSERT INTO #tempRecords VALUES (4,2,'Two')
INSERT INTO #tempRecords VALUES (4,5,'Five')
INSERT INTO #tempRecords VALUES (4,10,'Ten')

/** Create crosstab using PIVOT **/
SELECT *
FROM #tempRecords
PIVOT
(
MAX(BookValue)
FOR [BookNo]
IN ([1],[2],[3],[4],[5],[6],[7],[8])
)
AS p

DROP TABLE #tempRecords

Pivot Table - Example #1

CREATE TABLE #tempProducts
(
Supplier varchar(15),
Product varchar(20),
Product_Quantity integer
)
INSERT INTO #tempProducts VALUES ('HP','Software',300000)
INSERT INTO #tempProducts VALUES ('HP','Hardware',150000)
INSERT INTO #tempProducts VALUES ('HP','Monitor',250000)
INSERT INTO #tempProducts VALUES ('HP','Keyboard',500000)
INSERT INTO #tempProducts VALUES ('Apple','Software',20000)
INSERT INTO #tempProducts VALUES ('Apple','Hardware',125000)
INSERT INTO #tempProducts VALUES ('Apple','Monitor',800000)
INSERT INTO #tempProducts VALUES ('Apple','Keyboard',60000)
INSERT INTO #tempProducts VALUES ('Dell','Software',120000)
INSERT INTO #tempProducts VALUES ('Dell','Hardware',250000)
INSERT INTO #tempProducts VALUES ('Sony','Software',900000)
INSERT INTO #tempProducts VALUES ('Sony','Hardware',450000)
INSERT INTO #tempProducts VALUES ('Sony','Monitor',100000)
/** Create crosstab using PIVOT **/
SELECT *
FROM #tempProducts
PIVOT
(
SUM(Product_Quantity)
FOR [Product]
IN ([Software],[Hardware],[Monitor],[Keyboard])
)
AS p
DROP TABLE #tempProducts