CREATE TABLE Sales
(
	SalesID int IDENTITY PRIMARY KEY CLUSTERED,
	Product nvarchar(30),
	Quantity int,
	Quarter int
)
GO

-- Insert 12 rows of data
INSERT INTO Sales VALUES ('Swish', 15, 1)
INSERT INTO Sales VALUES ('Swish', 5, 2)
INSERT INTO Sales VALUES ('Attach-It', 36, 1)
INSERT INTO Sales VALUES ('Attach-It', 32, 2)
INSERT INTO Sales VALUES ('Attach-It', 41, 3)
INSERT INTO Sales VALUES ('Attach-It', 36, 4)
INSERT INTO Sales VALUES ('Paint', 12, 3)
INSERT INTO Sales VALUES ('Paint', 35, 4)
INSERT INTO Sales VALUES ('Rug', 15, 1)
INSERT INTO Sales VALUES ('Rug', 28, 2)
INSERT INTO Sales VALUES ('Rug', 34, 3)
INSERT INTO Sales VALUES ('Rug', 40, 4)
GO

SELECT * FROM Sales
GO

-- This query returns 12 rows, with lots of NULL values
SELECT
	Product, [1] AS 'Q1', [2] AS 'Q2', [3] AS 'Q3', [4] AS 'Q4'
FROM Sales
PIVOT
	(SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS pvt
GO

-- This query returns just four rows - but why is the extra step necessary?
WITH PivotView AS 
	(SELECT Product, Quantity, Quarter FROM Sales)
SELECT
	Product, [1] AS 'Q1', [2] AS 'Q2', [3] AS 'Q3', [4] AS 'Q4'
FROM [PivotView]
PIVOT
	(SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS pvt
GO

select * from authors
select * from jobs