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