Help with (Pivot/Cross-Join??) query to select a result set

I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.

Current structure is:

Colour Size Quantity
--------
Red 10 100
Red 12 200
Red 14 300
Blue 10 400
Blue 12 500
Blue 14 600
Green 10 700
Green 12 800
Green 14 900
Green 16 1000

I want to produce this result set:

Colour Size10 Size12 Size14 Size16
------------
Red 100 200 300 0
Blue 400 500 600 0
Green 700 800 900 1000

There could be any number of sizes or colours.

Is this possible? Can anyone give me any pointers?

Thanks in advance

gregNobody here is going to give a better explanation than that which is found in Books Online, which is excellent. Search BOL for "Crosstab".

I will tell you that it is almost always preferable to let your reporting interface handle pivoting your data, rather than having SQL Server do it. It is really more of a presentation issue.|||Thanks - that was useful.

I have the Query below which works. However, the Size is hardcoded into the CASE statements - is it possible to rewrite this so that I can get it to work for any number of sizes/size types, and create their corresponding columns automatically? I don't necessarily know all the possible sizes, and they may not be numerical...

Greg

--------

CREATE TABLE ClothTab( Colour VARCHAR(50), ClothSize INT, Quantity Int)
GO
INSERT INTO clothtab VALUES ('Red',10,100)
INSERT INTO clothtab VALUES ('Red', 12, 200)
INSERT INTO clothtab VALUES ('Red', 14, 300)
INSERT INTO clothtab VALUES ('Blue', 10, 400)
INSERT INTO clothtab VALUES ('Blue', 12, 500)
INSERT INTO clothtab VALUES ('Blue', 14, 600)
INSERT INTO clothtab VALUES ('Green', 10, 700)
INSERT INTO clothtab VALUES ('Green', 12, 800)
INSERT INTO clothtab VALUES ('Green', 14, 900)
INSERT INTO clothtab VALUES ('Green', 16, 1000)
GO

SELECT Colour,
SUM(CASE ClothSize WHEN 10 THEN Quantity ELSE 0 END) AS '10',
SUM(CASE ClothSize WHEN 12 THEN Quantity ELSE 0 END) AS '12',
SUM(CASE ClothSize WHEN 14 THEN Quantity ELSE 0 END) AS '14',
SUM(CASE ClothSize WHEN 16 THEN Quantity ELSE 0 END) AS '16'
FROM clothtab
GROUP BY Colour
ORDER By Colour DESC
GO|||Not without heavy and cumbersome SQL. But the point I was trying to make earlier is that any decent reporting tool (Crystal, Access, etc...) can easily format your data as a crosstab. You should let your interface handle presenting the data, and just use SQL Server for generating the data.|||I agree that presentation requirements would be better implemented on the reporting tool rather than in SQL.

Just a comment on your query though. Although it is possible to cascade CASE in a query, it would have some drawbacks. One is processing speed. Rather than casade your query with so many CASE, you might as well implement it in different SELECTs then combine them using JOIN.

The query might be longer but the better processing speed and simplicity of the query is worth it.|||I'd like to see some benchmarks on the two different approaches, since the one gregclark implemented is straight out of Books Online.

Seems to me the CASE method is making only a single pass through the dataset, while what you are suggesting would require a pass for each column.|||I agree that crosstabs are probably better left to Crystal, etc.

That said, how about generating the SQL code dynamically. E.g. preselect the sizes existing in the database, using that pre-selection to create the cases:

DECLARE @.SQL NVARCHAR(4000)
SET @.SQL = N'SELECT Colour'
SELECT @.SQL = @.SQL + N', SUM(CASE ClothSize WHEN ' +
CAST(ClothSize AS NVARCHAR) +
N' THEN Quantity ELSE 0 END) AS ''' + CAST(ClothSize AS NVARCHAR) + N'''' FROM
(
SELECT DISTINCT ClothSize FROM clothtab
) AS Sizes

SET @.SQL = @.SQL + N' FROM clothtab GROUP BY Colour ORDER By Colour DESC'
EXEC sp_executesql @.SQL|||As a matter of fact I'm comprising such a query right now, with the added challenge of allowing the user to customize the order of the columns. I just wouldn't recommend this for most SQL Developers.|||Thanks for your replies guys - point taken about using crystal/Excel etc.. however my colleague (for whom the question was on behalf of) wants to do it with SQL.

He has found some something useful here:
http://www.itrain.de/knowhow/sql/tsql/pivot/sp_transform_v1_1.asp

Greg