ansaurus

Question

Answer 1

A:

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

Tufo 2010-01-29 15:22:53

Hi Tufo it's working fine, but is it work fine i.e. performance point of view if i am having huge data?

Om 2010-01-29 15:29:14

if you have only those columns, so it'll work fine, it'll be 1 select for the whole table plus 2 for each item, no problem there. But if you'll have more columns, the performance can be a problem. How many PricingLevel registers do you have?

Tufo 2010-01-29 15:34:30

In that table i am having 15 columns but pricinglevel is fixed

Om 2010-01-29 15:39:55

fine, but how many pricing levels do you have?a good way for increasing the performance is creating an extra Index in this table for the column "PricingLevel" and after that create a View for this query.

Tufo 2010-01-29 15:44:25

yes, there is index already in table i am using that query in a viewThank you very much

Om 2010-01-29 16:15:45

Answer 2

+1 A:

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

shahkalpesh 2010-01-29 15:34:49

Om 2010-02-01 06:55:10

ansaurus

tags:

views:

answers:

get the formatted output

related questions