How to select records as columns in SQL

I have two tables: tblSizes and tblColors. tblColors has columns called ColorName, ColorPrice and SizeID. There is one size to multiple colors.开发者_开发问答 I need to write a query to select the size and all the colors (as columns) for a that size with the price of each size in its respective column.

The colors must be returned as columns, for instance:

SizeID : Width : Height : Red : Green : Blue  
1---------220-----220----£15----£20-----£29 

Hope this makes sense

Thank you

Edit: Tried the following code but not quite sure what's wrong:

DECLARE @Colors NVARCHAR(4000), @Query NVARCHAR(MAX)
SET @Colors = ''

SELECT @Colors = @Colors + '[' + C.Color +'],'
FROM tblTempProductSizesColors SC
INNER JOIN tblColors C on SC.ColorID=C.ID
GROUP BY Color
ORDER BY Color

SET @Colors = LEFT(@Colors,LEN(@Colors)-1)


SET @Query = '
SELECT *
FROM (  SELECT TS.Sizeid, TS.Width, TS.Height, TS.Depth, TC.Price
        FROM tblTempProductSizes TS
        INNER JOIN tblTempProductSizesColors TC
        ON TS.SizeId = TC.SizeId INNER JOIN tblColors C on TC.ColorID=C.ID) A
        PIVOT(SUM(Price) FOR C.Color IN ('+@Colors+')) AS PT'

EXEC sp_executesql @Query

---

select s.Sizeid, s.Width, s.Height, 
Red = SUM(CASE WHEN c.ColorName = "Red" THEN c.ColorPrice ELSE 0 END),
Blue = SUM(CASE WHEN c.ColorName = "Blue" THEN c.ColorPrice ELSE 0 END),
Green = SUM(CASE WHEN c.ColorName = "Green" THEN c.ColorPrice ELSE 0 END)
from tblSizes s
join tblColors c on c.SizeId = s.SizeId
group by s.Sizeid, s.Width, s.Height

---

If you don't want to write every color, and the expression for those columns, you can use Dynamic SQL if you have MSSQL 2005+ (first, be sure of take a look to this link).
UPDATED following comment

DECLARE @Colors NVARCHAR(4000), @Query NVARCHAR(MAX)
SET @Colors = ''

SELECT @Colors = @Colors + '[' + C.Color +'],'
FROM tblTempProductSizesColors SC
INNER JOIN tblColors C on SC.ColorID=C.ID
GROUP BY Color
ORDER BY Color

SET @Colors = LEFT(@Colors,LEN(@Colors)-1)


SET @Query = '
SELECT *
FROM (  SELECT TS.Sizeid, TS.Width, TS.Height, TS.Depth, TC.Price, C.Color
        FROM tblTempProductSizes TS
        INNER JOIN tblTempProductSizesColors TC
        ON TS.SizeId = TC.SizeId INNER JOIN tblColors C on TC.ColorID=C.ID) A
        PIVOT(SUM(Price) FOR Color IN ('+@Colors+')) AS PT'

EXEC sp_executesql @Query