开发者

get the formatted output

开发者 https://www.devze.com 2022-12-18 06:56 出处:网络
Having data as shown below ItemCodeSalesPricePricingLevel ITEM-000001 451.000000Barons ITEM-000001 432.000000开发者_如何学GoGuild

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
0

精彩评论

暂无评论...
验证码 换一张
取 消