By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T
([orders] varchar(3));
INSERT INTO T
([orders])
VALUES
('S1'),
('S2'),
('S3'),
('S4'),
('S5'),
('S6'),
('S7'),
('S8'),
('S9'),
('S10'),
('S11'),
('S12');
12 rows affected
WITH CTE AS
(
SELECT Orders,
(ROW_NUMBER() OVER(ORDER BY LEN(Orders)) - 1) / 5 RN
FROM T
)
SELECT STRING_AGG(Orders, ',') Orders
FROM CTE
GROUP BY RN
ORDER BY RN;
| Orders |
|---|
| S1,S2,S3,S4,S5 |
| S6,S7,S8,S9,S10 |
| S11,S12 |
WITH CTE AS
(
SELECT Orders,
(ROW_NUMBER() OVER(ORDER BY LEN(Orders)) - 1) / 5 RN
FROM T
)
SELECT STUFF(
(
SELECT ',' + Orders
FROM CTE
WHERE RN = TT.RN
FOR XML PATH('')
), 1, 1, ''
) Orders
FROM CTE TT
GROUP BY RN
ORDER BY RN;
| Orders |
|---|
| S1,S2,S3,S4,S5 |
| S6,S7,S8,S9,S10 |
| S11,S12 |