How to make a line break in a stuff function when using DISTINCT-CodePudding

I have a stuff function that concatenates multiple records and I put a line break after every second record and its works fine with this query:

STUFF((
    SELECT   CASE WHEN ROW_NUMBER() OVER (order by new_name) % 2 = 1 THEN CHAR(10) ELSE ',' END     new_name
    FROM new_subcatagories
       FOR XML PATH('')), 1, 1, '')

and the result is

Auditory,Kinesthetic  vestibular  
Multitasking,Planning & organization  
Proprioception,Tactile  
Vestibular tactile,Visual

But I want now to make this with a other column that I need to DISTINCT and I can't get it work my query is:

STUFF((
    SELECT  distinct (CASE WHEN ROW_NUMBER() OVER (order by new_maincatgoriesname) % 2 = 1 THEN CHAR(10) ELSE ',' END
         new_maincatgoriesname)
       FOR XML PATH('')), 1, 1, '')

and I get the result is in multiple not expected ways for example

Executive Function
Sensory  Discrimination
Sensory modulation ,Multitasking,Sensory  Discrimination,Sensory modulation

or other not expected ways, and I want the result to be

Executive Function,Sensory Discrimination          
Sensory modulation,Multitasking

If someone can help my it will be really appreciated.

CodePudding user response：

DISTINCT applies to the entire row so having an extra column populated with unneeded data (such as ROW_NUMBER()) would give invalid results.

To fix it you need to add another query nesting level.

DECLARE @Blah TABLE( new_maincatgoriesname VARCHAR( 200 ))
INSERT INTO @Blah
VALUES( 'Executive Function' ), ( 'Sensory Discrimination' ), ( 'Multitasking' ),
    ( 'Sensory Discrimination' ), ( 'Executive Function' ), ( 'Sensory modulation' )

SELECT
    STUFF( CAST((
    -- Step 2: manipulate result of Step 1
    SELECT (CASE WHEN ROW_NUMBER() OVER (order by new_maincatgoriesname) % 2 = 1 THEN CHAR(10) ELSE ',' END   new_maincatgoriesname )
    FROM
        -- Step 1: Get distinct values
        ( SELECT DISTINCT new_maincatgoriesname
        FROM @Blah ) AS MainQuery
      FOR XML PATH('') ) AS VARCHAR( 2000 )), 1, 1, '' )

Output:

Executive Function,Multitasking
Sensory Discrimination,Sensory modulation