Combining results of multiple rows based on group? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Combining results of multiple rows based on group?

Author

Topic

Bazinga
Starting Member

19 Posts

Posted - 2013-07-17 : 11:38:57

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

Probably not too difficult, but I'm not sure how.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 11:43:00

[code]
SELECT ID,[Group],
STUFF((SELECT ',' + Value
FROM Table
WHERE ID = t.ID
AND [Group] = t.[Group]
FOR XML PATH('')),1,1,'') AS CombinedValues
FROM (SELECT DISTINCT ID,[Group] FROM Table)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bazinga
Starting Member

19 Posts

Posted - 2013-07-17 : 16:07:16

Thanks. I am creating a view and using it in reporting software. The combined field seems to be behaving differently then the other data. Can I format the combined data field different, say instead of XML I want it to be as a string? Or go about this in a different way?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-07-17 : 16:15:25

If you're using a reporting tool then generally you just get the data in "normal" relational format and deal with all the presentation in the tool. That would include stringing all your grouped data into a single value.

Bazinga
Starting Member

19 Posts

Posted - 2013-07-17 : 16:40:35

I've noticed that the combined value data type is 'memo'. I can probably convert this in the reporting software, but it would be easier to just do in in SQL first.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 00:58:18

quote:
Originally posted by Bazinga

Thanks. I am creating a view and using it in reporting software. The combined field seems to be behaving differently then the other data. Can I format the combined data field different, say instead of XML I want it to be as a string? Or go about this in a different way?

It will be returned as a string itself. The usage of FOR XML is just a hack to get result as a delimited list. Did you try it at all?
Any issues you faced?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources