![]() ![]() notation worked out fastest in my tests YMMV. Here are the results of all seven approaches in tabular form: ApproachĪverage duration, in milliseconds, for all approachesĪlso note that the variations on FOR XML PATH were tested independently but showed very minor differences so I just combined them for the average. You may notice a couple of contenders missing the UDF using DISTINCT and the recursive CTE were so off the charts that they would skew the scale. Now again, admittedly, I did not set out to compare every single approach conceived in the history of SQL Server just the few highlights I posted above. name įor me, this was 575 objects, with 7,080 total rows the widest object had 142 columns. There have been many ways to solve this problem over the years here are just a few, based on the following sample data: ![]() Some might call this a "horizontal join." A quick visual example demonstrating how we would compress a list of pets belonging to each family member, from the normalized source to the "flattened" output: What is Grouped Concatenation?įor the uninitiated, grouped concatenation is when you want to take multiple rows of data and compress them into a single string (usually with delimiters like commas, tabs, or spaces). ** UPDATE January 2017 **: STRING_AGG() will be in SQL Server 2017 read about it here, here, and here. Connect #728969 : Ordered Set Functions – WITHIN GROUP Clause (Closed as Won't Fix).Connect #247118 : SQL needs version of MySQL group_Concat function (Postponed).It has been requested, but no success yet, as evidenced in these Connect items: Grouped concatenation is a common problem in SQL Server, with no direct and intentional features to support it (like XMLAGG in Oracle, STRING_AGG or ARRAY_TO_STRING(ARRAY_AGG()) in PostgreSQL, and GROUP_CONCAT in MySQL).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |