Having data as shown below
ItemCode SalesPrice PricingLevel
ITEM-000001 451.000000 Barons
ITEM-000001 432.000000 开发者_如何学GoGuild
Is there is a way to get the following output:
ItemCode Barons Guild
ITEM-000001 451 432
SELECT ItemCode,
Sum(Case when PricingLevel = 'Barons' Then SalesPrice else 0 end) as Barons,
Sum(Case when PricingLevel = 'Guild' Then SalesPrice else 0 end) as Guild
FROM myTable
GROUP BY ItemCode
Just try this (note: as i dont know your table name, i called it "Items"):
SELECT DISTINCT I1.ItemCode,
(SELECT SalesPrice FROM Items I2 WHERE I2.ItemCode = I1.ItemCode AND I2.PricingLevel = 'Barons') Barons,
(SELECT SalesPrice FROM Items I3 WHERE I3.ItemCode = I1.ItemCode AND I3.PricingLevel = 'Guild') Guild
FROM Items I1
For not showing the decimal zeros, use the following:
SELECT DISTINCT I1.ItemCode,
(SELECT CAST(SalesPrice AS DECIMAL(10,0)) FROM Items I2 WHERE I2.ItemCode = I1.ItemCode AND I2.PricingLevel = 'Barons') Barons,
(SELECT CAST(SalesPrice AS DECIMAL(10,0)) FROM Items I3 WHERE I3.ItemCode = I1.ItemCode AND I3.PricingLevel = 'Guild') Guild
FROM Items I1
精彩评论