Link to home
Start Free TrialLog in
Avatar of SQLSearcher
SQLSearcher

asked on

SQL Vertical table to Horizontal table

Hello Experts Exchange
I have a table of data that I need to group by category, I have attached a Excel spreadsheet with two tables.

Table 1 is how I have the data, and I need the SQL Script to get table 2.

Regards

SQLSearcher
Sample-Data.xls
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image
DECLARE @SampleData TABLE
(
	Sale_Date	DATE,
	Shop_Ref	INT,
	Category	VARCHAR(50),
	Price_Paid	MONEY
);

INSERT INTO @SampleData (Sale_Date, Shop_Ref, Category, Price_Paid)
VALUES
	('11/5/2015', 1, 'Cat1', 3	),
	('11/5/2015', 1, 'Cat1', 3	),
	('11/5/2015', 1, 'Cat1', 4	),
	('11/5/2015', 1, 'Cat2', 5	),
	('11/5/2015', 2, 'Cat2', 2	),
	('11/5/2015', 2, 'Cat3', 12	),
	('11/5/2015', 2, 'Cat3', 10	),
	('11/5/2015', 3, 'Cat1', 2	),
	('11/5/2015', 3, 'Cat1', 3	),
	('11/6/2015', 3, 'Cat1', 4	),
	('11/6/2015', 3, 'Cat1', 5	);

SELECT Sale_Date, Shop_Ref, ISNULL([Cat1], 0) AS Cat1, ISNULL([Cat2], 0) AS Cat2, ISNULL([Cat3], 0) AS Cat3
FROM
(
	SELECT *
	FROM @SampleData
) AS S
PIVOT
(
	SUM(Price_Paid)
	FOR Category IN ([Cat1], [Cat2], [Cat3])
) AS P

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SQLSearcher
SQLSearcher

ASKER

Thank you for your help.