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 youEdit: 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
精彩评论