Combine values that contribute to aggregation in SQL

Question

SQL2008R2 Express...

Provided I had a row of data that appeared as such...

Value   ID  User
10      5   1
11      7   1
22      5   2
 9      9   2

And I wanted to run a query as such to determine what the sum of value was by user...

SELECT [user], SUM([value])[value] FROM [MyTable] GROUP BY [user]

which would yield

User   Value
1      32
2      31

All is well, but now say I wanted a representation of what values from the [ID] column contributed to that total?

The scenario is that users perform various tasks that have values ultimately to be summed, averaged, etc.... And I wish to know what specific task lead to the total I am seeing. Essentially a DISTINCT list of all the rows compressed into the aggregation, delimited in some way of course so I can distinguish / parse them on another level. end result would look something like this...

User   Value   IDs
1      32      5,7
2      31      5,9

Is this doable without going RBAR with temporary tables?

look at : http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings — Mostafa Armandi, Nov 26 '13 at 16:05
Ok, more complicated than I had originally thought, will test your and the solution provided by gotqn as soon as possible and let you guys know, thank you. — Sabre, Nov 26 '13 at 17:06
This solution may have worked, however the below was more concise, and solved the issue so I did not fully test past a workable solution. Thank you however for the contribution. — Sabre, Nov 26 '13 at 18:18

score 0 · Accepted Answer · answered Nov 26 '13 at 16:26

Try this:

DECLARE @DataSource TABLE
(
     [Value] TINYINT
    ,[ID] TINYINT
    ,[User] TINYINT
)

INSERT @DataSource ([Value], [ID], [User])
VALUES (10,5,1)
      ,(11,7,1)
      ,(22,5,2)
      ,(9,9,2)

SELECT DS.[User]
      ,SUM(DS.[Value])
      ,SUBSTRING((SELECT DISTINCT ',' + CAST([ID] AS VARCHAR(4)) FROM @DataSource WHERE [User] = DS.[User] FOR XML PATH('')),2,4000)
FROM @DataSource DS
GROUP BY [User]

The above is full working example and generally what is new is the following line:

SUBSTRING((SELECT DISTINCT ',' + CAST([ID] AS VARCHAR(4)) FROM @DataSource WHERE [User] = DS.[User] FOR XML PATH('')),2,4000)

It is a ordinary way of concatenating values.

Ok, more complicated than I had originally thought, will test your and the solution provided by pathfinder as soon as possible and let you guys know, thank you. — Sabre, Nov 26 '13 at 17:05
The definitely did exactly what I was looking for, thank you. — Sabre, Nov 26 '13 at 18:16

Combine values that contribute to aggregation in SQL

1 Answers1