CODEWITHSUNDEEP
sqlsql-server-2008
user2864154
user2864154

Reputation: 475

Distribute one row of data to two rows in SQL

I have the table below. Is there a good way to regroup them such that group 'a+b' gets double counted and added to group 'a' and 'b'

Group Amount
  a     100
  b     200
  c     300
 a+b    400

The result would be:

Group Amount
  a    500
  b    600
  c    300

Upvotes: 1

Views: 43

Answers (1)

Paul Abbott
Paul Abbott

Reputation: 7211

This is hideous, but you could do a subquery for matching groups.

SELECT GroupId,
Amount + ISNULL(
  (SELECT SUM(Amount) FROM MyGroups t2
   WHERE t2.GroupId <> t1.GroupId
   AND t2.GroupId LIKE '%' + t1.GroupId + '%'), 0)
FROM MyGroups t1
WHERE t1.GroupId NOT LIKE '%+%'

http://sqlfiddle.com/#!3/50214/8

Upvotes: 2

Related Questions