Insert if - 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 2000 Forums

Transact-SQL (2000)

Insert if

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-22 : 07:35:30

Khal writes "I have been stuck on this for ages.

I have a table eg

name ID Amount
dave a2 70
dave a2 75
john b2 100
john b2 50

i have to insert this data in a new table and look like this:

name ID Total AmountD Total AmountJ
dave a2 145 0
john b2 0 100

im using the insert statement but it wont work, I want one column to total all values for ID a2 and the other column to calculate total for ID b2"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-22 : 07:46:25

SELECT name, ID, Sum(CASE WHEN ID='a2' THEN Amount ELSE 0 END) AS AmountD, Sum(CASE WHEN ID='b2' THEN Amount ELSE 0 END) AS AmountJ
FROM myTable
GROUP BY name, ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources