To make combine the multiple rows into either one row or two rows by comma separator as per the specified length in SQL server

Question

I need to combine the multiple rows into either single row or two rows by comma separator as per the specified length as below:

+-------------------------------------+--------------------------------+ 
   ID                                         Name
+-------------------------------------+--------------------------------+ 
    1                                       Rohit
    1                                       Virat
    1                                       Dhawan
    1                                       Vijay
    2                                       Mohit
    2                                       Raina
+-------------------------------------+--------------------------------+

Here we are trying to combine multiple rows into single row but it needs to be with condition that if the first rows crosses the length of 12 then insertion of new record as need to be shown as below:

+-------------------------------------+--------------------------------+ 
         ID                                         Name
+-------------------------------------+--------------------------------+ 
        1                                       Rohit,Virat
        1                                       Dhawan,Vijay
        2                                       Mohit,Raina
+-------------------------------------+--------------------------------+

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Nov 20 '19 at 14:02
*"So kindly if any know the solutions plz mention it in comments"* Comments are not the place answers; the answer section is. — Thom A, Nov 20 '19 at 14:26

Thom A · Answer 1 · 2019-11-20T14:46:48.000

One idea, using a window function to get the cumulative length:

WITH Grps AS(
    SELECT V.ID,
           V.[Name],
           (SUM(LEN(V.[Name])) OVER (PARTITION BY V.ID ORDER BY [Name]) -1 ) / 12 AS Grp
    FROM (VALUES(1,'Rohit'),
                (1,'Virat'),
                (1,'Dhawan'),
                (1,'Vijay'),
                (2,'Mohit'),
                (2,'Raina'))V(ID, [Name]))
SELECT G.ID,
       STUFF((SELECT ',' + sq.[name]
              FROM Grps sq
              WHERE sq.ID = G.ID
                AND sq.Grp = G.Grp
              FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,1,'') AS Names
FROM Grps G
GROUP BY G.ID,
         G.Grp;

This won't be flawless, however, but the alternative approach would be to have to use an rCTE to reset the count each time it's over 12; which will be far slower.

Note, as there is no way to retain the order in your table, due to no column to order by, the names will be in alphabetical order.

As mentioned, if you must use an iterative (more accurate but far slower) solution.:

WITH RNs AS(
    SELECT V.ID,
           V.[Name],
           ROW_NUMBER() OVER (PARTITION BY V.ID ORDER BY V.[Name]) AS RN
    FROM (VALUES(1,'Rohit'),
                (1,'Virat'),
                (1,'Dhawan'),
                (1,'Vijay'),
                (2,'Mohit'),
                (2,'Raina'))V(ID, [Name])),
Grps AS(
    SELECT R.ID,
           R.[Name],
           R.RN,
           LEN(R.[Name]) AS [Length],
           1 AS Grp
    FROM RNs R
    WHERE R.RN = 1
    UNION ALL
    SELECT R.ID,
           R.[name],
           R.RN,
           CASE WHEN LEN(R.[Name]) + G.[Length] > 12 THEN LEN(R.[Name]) ELSE LEN(R.[Name]) + G.[Length] END,
           CASE WHEN LEN(R.[Name]) + G.[Length] > 12 THEN G.Grp + 1 ELSE G.Grp END
    FROM RNS R
         JOIN Grps G ON R.ID = G.ID
                    AND R.RN = G.RN + 1)
SELECT G.ID,
       STUFF((SELECT ',' + sq.[name]
              FROM Grps sq
              WHERE sq.ID = G.ID
                AND sq.Grp = G.Grp
              FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,1,'') AS Names
FROM Grps G
GROUP BY G.ID,
         G.Grp;

To make combine the multiple rows into either one row or two rows by comma separator as per the specified length in SQL server

1 Answers1