Best way to concatenate column values in aggregate

debugcn Published at Dev

Traceur

I have a table that looks like

srvc_dt     fclity_id  clmt_key rfrsh_dt    srvc_cd
2013-01-16  000167404   2       2013-01-31  01204
2013-01-16  000167404   2       2013-01-31  11112
2011-01-11  000167404   2488    2013-02-28  11113
2013-01-08  000167404   6070    2013-01-31  11113
2013-01-08  000167404   28371   2013-01-31  01202
2013-01-08  000167404   28371   2013-01-31  11107
2013-01-08  000167404   28371   2013-01-31  11112
2013-01-08  000167404   28371   2013-01-31  11117
2013-01-08  000167404   28371   2013-01-31  12101
2012-07-17  000167404   40571   2013-01-31  11113
2012-11-19  000167404   40571   2013-01-31  11113
2013-01-14  000167404   44189   2013-01-31  27211
2010-07-06  000167404   46396   2013-02-28  01202
2010-07-06  000167404   46396   2013-02-28  02142
2010-07-06  000167404   46396   2013-02-28  11107
2010-07-06  000167404   46396   2013-02-28  11112
2013-01-03  000167404   113264  2013-01-31  01204
2013-01-03  000167404   113264  2013-01-31  23323
2013-01-07  000167404   113390  2013-01-31  01202
2013-01-07  000167404   113390  2013-01-31  02142

and I would like to transform it into something like

srvc_dt     fclity_id  clmt_key rfrsh_dt      srvc_cd
2010-07-06  000167404   46396   2013-02-28    01202,02142,11107,11112
2011-01-11  000167404   2488    2013-02-28    11113
2012-07-17  000167404   40571   2013-01-31    11113
2012-11-19  000167404   40571   2013-01-31    11113
2013-01-03  000167404   113264  2013-01-31    01204,23323
2013-01-07  000167404   113390  2013-01-31    01202,02142
2013-01-08  000167404   6070    2013-01-31    11113
2013-01-08  000167404   28371   2013-01-31    01202,11107,11112,11117,12101
2013-01-14  000167404   44189   2013-01-31    27211
2013-01-16  000167404   2       2013-01-31    01204,11112

what would be the most performant (for > 100 000 000 rows) way to accomplish this in SQL SERVER 2012?

PIVOT? FOR XML PATH? dynamic case statements? something else?

EDIT: while Simulating group_concat MySQL function in Microsoft SQL Server 2005? does display 1 way to accomplish this, I'm looking for performance comparisons between different methods

Tim Lehner

Grouped Concatenation in SQL Server by Aaron Bertrand offers a good look at what you want.

I normally don't like link-only answers, but since you're looking for the performance comparison of various techniques, there's no reason to duplicate a huge article here.

I also agree with the comments which say you'll ultimately have to test these solutions on your system with your data anyway. If performance really matters, you'll have to measure for yourself.

Collected from the Internet

Please contact [email protected] to delete if infringement.