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:
- we are using
ROW_NUMBER function to sort the rows and know which three consequence rows form a group
- 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.
