New rows based on existing rows

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

General SQL Server Forums

New to SQL Server Programming

New rows based on existing rows

Author

Topic

pravin14u
Posting Yak Master

246 Posts

Posted - 2007-07-25 : 09:03:20

Hi All,

I have the following Table

Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 30

Now, i need to add four more rows to the table

Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 35
z1 Total 15 (xM1+XM2)
z1 Diff 5 (xM3-xM1+XM2)
z2 Total 25 (yM1+yM2)
z2 Diff 10 (yM3-yM1+yM2)

Please help me.

Many Thanks,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-25 : 09:21:53

[code]INSERT INTO TheTable(Type, Name, Value)
SELECT 'z1', 'Total', SUM(Value)
FROM TheTable
WHERE Type = 'x'
AND Name IN ('M1', 'M2')

UNION ALL

SELECT 'z1', 'Diff', SUM(CASE WHEN Name IN ('M1', 'M2') THEN -Value ELSE Value END)
FROM TheTable
WHERE Type = 'x'[/code]

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

pravin14u
Posting Yak Master

246 Posts

Posted - 2007-07-25 : 09:40:52

Thanks a lot, Its working fine!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources