how to insert a line break after every second loop in t-sql stuff function

December 10, 2021

how can i insert a CHAR(10) after every second loop in t-sql stuff function in my query

  SELECT  STUFF(
                (
                    SELECT  ', ' + new_name 
                   FROM new_subcatagories
                   FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 2, '')

so the result should by

record1,record2,

record3,record4

>Solution :

You could use a CASE expression to see if the value of ROW_NUMBER is divisible by 2, and if not then add a carriage return and line break:

SELECT STUFF((SELECT CASE WHEN ROW_NUMBER() OVER (order by new_nam) % 2 = 1 THEN CHAR(13) + CHAR(10) ELSE '' END + ', ' + new_nam
              FROM new_subcatagories
              ORDER BY new_nam
              FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 4, '');