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

user_17607690 0
Viewed 29

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.

tsql sql-server sql
1 Answers

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
user_63052940
Related