Group concat in sql server for multiple column groups performance

Question

I am currently working in SQL server 2005 and table contain million of rows. The table have following rows and columns

ID      PO_ID   Event_ID  Item_ID 
1        22        970     123456
1        22        970     123457
1        23        970     1234589
1        22        971     12345790
1        22        971     12345792

I want to concat column item_ID for multiple column group "ID, PO_ID, Event_ID" The output Will be like this

 ID      PO_ID   Event_ID    Item_ID 
  1        22        970     123456,123457
  1        23        970     1234589
  1        22        971     12345790,12345792

I have the following SQL query

select ID, PO_ID, Event_ID,
       substring(
                   ( SELECT ','+ Item_ID)
                    FROM table as a
                    WHERE a.ID=table.ID
                    AND  a.PO_ID=table.PO_ID
                    and a.event_ID=table.event_ID
                    FOR XML PATH ('') 
                     )
from table 
group by ID,PO_ID,Event_ID;

But this query is really slow in terms of performance Is there any optimized way to do this in SQL server 2005? Any help will be appreciated.

Note : I don't have permissions to create UDF or indexes.

did you at least try to search for a solution? it took me just a few seconds to find this: https://stackoverflow.com/questions/15154644/group-by-to-combine-concat-a-column — Esteban P., Jun 20 '17 at 20:33
Possible duplicate of [Concatenate one field after GROUP BY](https://stackoverflow.com/questions/13647394/concatenate-one-field-after-group-by) — Esteban P., Jun 20 '17 at 20:33
Thanks for you reply yes I search for a solution and I have the same SQL query for the above issue . According to solution above 1) It is using stuff function not supported for SQL server 2005 . 2) My query is same as that of solution except substrings. I just asked for performance suggestions as table have million of rows — Awais Sheikh, Jun 20 '17 at 20:37
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Sean Lange, Jun 20 '17 at 20:41
I has also Used cross apply solution instead of that but performance was really slow — Awais Sheikh, Jun 20 '17 at 20:43
The best solution - in my opinion - is to avoid such concatenation. — Bogdan Sahlean, Jun 20 '17 at 21:01
SQL Server 2005 has been out of regular support since 2011, and out of extended support since April 2016. Your best course is probably to upgrade to a supported product with the features that you need to get the job done. — Lord Peter, Jun 21 '17 at 20:18

score 0 · Answer 1 · answered Jun 20 '17 at 21:18

I would recommend writing this as:

select ID, PO_ID, Event_ID,
       stuff( (select ',' + a.Item_ID)
               from table a
               where a.ID = t.ID and
                     a.PO_ID = t.PO_ID and
                     a.event_ID = t.event_ID
               FOR XML PATH ('') 
              ), 1, 1, ''
             ) as items
from (select distinct ID, PO_ID, Event_ID
      from table t
     ) t;

Then for performance, you want an index on (ID, PO_ID, Event_ID, Item_Id).

score 0 · Answer 2 · answered Jun 21 '17 at 19:35

0

It's possible you create an index on the table this could help you

answered Jun 21 '17 at 19:35

Javier Jimenez Matilla

302
1
7

Group concat in sql server for multiple column groups performance

2 Answers2