case statement and group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

case statement and group by

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 09:58:39

SELECT T1.[ItemCode], MAX(case when T1.PriceList=1 then T1.Price end) as 'First Cost', MAX(case when T1.PriceList=2 then T1.Price end) as 'Wholesale', MAX(case when T1.PriceList=2 then T1.Price end) as 'Retail'
FROM ITM1 T1

How can I group by this code so this just gives me the the ItemCode and 3 prices on 1 row?

When I use this GROUP by below:

GROUP BY T1.ItemCode, (case when T1.PriceList=1 then T1.Price end), (case when T1.PriceList=2 then T1.Price end), (case when T1.PriceList=2 then T1.Price end)

It spreads out the prices on 3 rows..

and when I only put the ItemCode in the GROUP BY, It gives me values where there should be a zero

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-21 : 10:04:42

[code]
SELECT T1.[ItemCode],
MAX(case when T1.PriceList=1 then T1.Price end) as 'First Cost',
MAX(case when T1.PriceList=2 then T1.Price end) as 'Wholesale',
MAX(case when T1.PriceList=2 then T1.Price end) as 'Retail'
FROM ITM1 T1
GROUP BY T1.[ItemCode][/code]

KH
[spoiler]Time is always against us[/spoiler]

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 10:05:17

"when I only put the ItemCode in the GROUP BY, It gives me values where there should be a zero"

can you post some sample data?

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-21 : 10:11:25

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources