Saturday, March 31, 2012 9:16 AM
I have a table which looks like
URL, Date, ActivityType, TextValue
My requirement is to first issue a query which groups the results by URL, Date and ActivityType
Now I don't want to sum/count the text value. the records which match the above grouping should be converted into a comma separated string.
The net output of query is
URL1, 01/01/2000, 1, "a, b, c, d"
URL1, 01/02/2000, 2, "e, f, g, h"
Here a, b, c, d, e, f, g h are values taken from the column TextValue and concatenated for the URL, Date and Activity Grouping.
Edit: I saw some examples on concatenation using variables and coalesce function... but I don't know how to use in a group query because it expects aggregate functions for anything which is not a part of groupby...
MSDNStudent Knows not much!
- Edited by MSDN Student Saturday, March 31, 2012 9:21 AM
Saturday, March 31, 2012 9:38 AM
The FOR XML solution is the one that is the most commonly used and recommended.
Erland Sommarskog, SQL Server MVP, email@example.com
- Marked As Answer by MSDN Student Saturday, March 31, 2012 10:49 AM
Saturday, March 31, 2012 1:40 PMModerator
Look at the EXAMPLE 4 - Using XML PATH & correlated subquery for sublist:
Very similar to your requirement.
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Saturday, October 06, 2012 5:24 AM