Group_Concat in pairs of nth

Question

I have a query that I normally group_concat. I need to know if dbo.group_concat has the ability to pair the result of a query into pairs of 3 (as an example).

For example:

select size, pattern, dbo.group_concat(mass) mass 
from labels 
group by size, pattern

Result is

Size Pattern Mass 
-----------------------------------------------------------
234  ZYL     22.43,55.32,33.24,22.53,56.32,40.32,50.21,32.21
234  ZA      50.00,56.23,21.23,50.21

I'd like to have this result

Size Pattern  Mass
--------------------------------
234  ZYL      22.43,55.32,33.24
234  ZYL      22.53,56.32,40.32
234  ZYL      50.21,32.21
234  ZA       50.00,56.23,21.23
234  ZA       50.21 

Answer 1

This can be done easily, if we have a group ID column. This column will group rows per three, sorting the data by [mass] value. Since, we have not got such column, we need to calculated it. The steps as are as following:

1. we are using ROW_NUMBER function to sort the rows and know which three consequence rows form a group
2. then, we are using recursive common table expression (nothing too complicated) - just getting the first row of each size, pattern entity and then join the next row ... then join the next, until we get them all

This is full working example of the above:

DECLARE @DataSource TABLE
(
    [size] VARCHAR(12)
   ,[pattern] VARCHAR(12)
   ,[mass] DECIMAL(9,2)
);


INSERT INTO @DataSource ([size], [pattern], [mass])
VALUES (234, 'ZYL', 22.43)
      ,(234, 'ZYL', 55.32)
      ,(234, 'ZYL', 33.24)
      ,(234, 'ZYL', 22.53)
      ,(234, 'ZYL', 56.32)
      ,(234, 'ZYL', 40.32)
      ,(234, 'ZYL', 50.21)
      ,(234, 'ZYL', 32.21)
      --
      ,(234, 'ZA', 50.00)
      ,(234, 'ZA', 56.23)
      ,(234, 'ZA', 21.23)
      ,(234, 'ZA', 50.21);

WITH DataSource AS 
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY [size], [pattern] ORDER BY [mass]) AS [RowID]
    FROM @DataSource
),
RecursiveDataSource AS
(
    SELECT [size], [pattern], [mass], [RowID]
          ,1 AS [Group]
    FROM DataSource
    WHERE [RowID] = 1
    UNION ALL
    SELECT A.*
          ,R.[Group] + CASE WHEN (A.[rowID] - 1) % 3 = 0 THEN 1 ELSE 0 END
    FROM DataSource A
    INNER JOIN RecursiveDataSource R
        ON A.[size] = R.[size]
        AND A.[pattern] = R.[pattern]
        AND A.[RowID] - 1 = R.[rowID]
)
SELECT *
FROM RecursiveDataSource
ORDER BY [pattern], [RowID];

Now, in the final SELECT we just need to add your initial code, but grouping by the [group] column also:

SELECT [size], [pattern], [dbo].[group_concat] ([mass])
FROM RecursiveDataSource
GROUP BY [size], [pattern], [Group];

In my system, the above yields this:

Some considerations:

• I am ordering the values by the mass value (and in your example, you are not) - this is completely up to you; you can change the ordering to whatever you need, even using SELECT 1 in the ORDER BY clause of the ROW_NUMBER function to get some random ordering;
• using recursive CTE for large table can lead to bad performance; be sure have tested the code with your real data first;
• the concatenate SQL CLR function on my systems allows setting order; in my case, I have concatenated the rows using the RowID value; if your function does not allow specifying such order you rely on the .net code behind it, so you can get different order of the values in the final CSV list.