Aggregate column items when IDs have same value

Question

Starting with 1:

ID      HistID  Item
5       361     Joe Smith
5       376     Prince Brown
34      250     Sue Blue
306     106     Jane Doe
306     238     Sandy Pink
392     390     Dawn Red
512     363     Gus Green
512     515     Joy Orange
512     49      Sam Strong

What is a suitable Linq or T-Sql query giving 2:

ID      Item
5       Joe Smith,[br/]Prince Brown
34      Sue Blue
306     Jane Doe,[br/]Sandy Pink
392     Dawn Red
512     Gus Green,[br/]Joy Orange,[br/]Sam Strong

where ID, HistID and Item are table column names or object properties

If the answer is the Linq variant then the result set must still be of IQueryable, so that it can used in further queries allowing lazy evaluation.

In both cases an acceptable answer may have each Item ending with a ,[br/] as well

PS: I am right in thinking that the .Aggregate() method often used with Linq will not allow further lazy evaluation?

I have the t-sql solution now. The best explantion being here: http://knowledgebaseworld.blogspot.com/2008/12/string-concatenation-in-sql.html — user1040323, Nov 10 '11 at 18:29

score 1 · Accepted Answer · answered Nov 10 '11 at 21:03

try this:

--Concatenation with FOR XML & eliminating control/encoded char expansion "& < >"
set nocount on;
declare @YourTable table (ID int, HistID int, Item varchar(25))
insert into @YourTable VALUES (5  ,361,'Joe Smith')
insert into @YourTable VALUES (5  ,376,'Prince Brown')
insert into @YourTable VALUES (34 ,250,'Sue Blue')
insert into @YourTable VALUES (306,106,'Jane Doe')
insert into @YourTable VALUES (306,238,'Sandy Pink')
insert into @YourTable VALUES (392,390,'Dawn Red')
insert into @YourTable VALUES (512,363,'Gus Green')
insert into @YourTable VALUES (512,515,'Joy Orange')
insert into @YourTable VALUES (512,49 ,'Sam Strong')
set nocount off

SELECT
    t1.ID
        ,STUFF(
                   (SELECT
                        ', <br/>' + t2.Item
                        FROM @YourTable t2
                        WHERE t1.ID=t2.ID
                        ORDER BY t2.Item
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,7, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.ID

OUTPUT:

ID          ChildValues
----------- ---------------------------------------------
5           Joe Smith, <br/>Prince Brown
34          Sue Blue
306         Jane Doe, <br/>Sandy Pink
392         Dawn Red
512         Gus Green, <br/>Joy Orange, <br/>Sam Strong

(5 row(s) affected)

I'm not sure why the OP had [br/] in their output, I used <br/> which can be easily changed if they want.

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

Well, in oracle you could do the below... SQL server has a similar function depending on version and oracle has a str_agg function in 11g.

So.. what engine?

Select ID, WM_CONCAT(Item)
From table

Previous Stack answer: -->Concat field value to string in SQL Server

I'll play around to make it work for your example here in a bit.

This should be close I've not tested it though;

SELECT
ID, STUFF(
 (SELECT ',' + Item 
  FROM [TableName]
   FOR XML PATH('')), 1, 1, '') AS 'Item'
FROM [TableName]

edited May 23 '17 at 12:11

Community

1
1

answered Nov 10 '11 at 18:16

xQbert

34,733
2
41
62

the join is messed up, you end up with every name for every row. – KM. Nov 10 '11 at 21:07

Aggregate column items when IDs have same value

2 Answers2