STUFF only unique values along with sorting another column

I want to combine values with comma-separated. For that used stuff.
But my data has duplicate values, and I just need unique items from that.

Here is a query that I’m using.

SELECT STUFF
(
    RTRIM
    (
        (   SELECT  N', ' + CAST(column1Name AS varchar(MAX))
            FROM dbo.tableName
            ORDER BY column2Name
            FOR XML PATH (N'') 
        ) 
    )
, 1, 2, N'')

I tried SELECT DISTINCT within STFF, but that requires a column that used for sorting within SELECT clause, I am using STUFF so I can’t use that column in SELECT clause.

SELECT STUFF
(
    RTRIM
    (
        (   SELECT DISTINCT N', ' + CAST(column1Name AS varchar(MAX))
            FROM dbo.tableName
            ORDER BY column2Name
            FOR XML PATH (N'') 
        ) 
    )
, 1, 2, N'')

I also tried to use sub-query to do sorting and use distinct outside, but that also gave a compile error.

SELECT STUFF
(
    RTRIM
    (
        (   SELECT DISTINCT N', ' + CAST(column1Name AS varchar(MAX))
            FROM
            (
                SELECT column1Name
                FROM dbo.tableName
                ORDER BY column2Name
            ) tableAlias
            FOR XML PATH (N'') 
        ) 
    )
, 1, 2, N'')

I also tried GROUP BY, which also forces me to add column2Name in the SELECT clause.

SELECT STUFF
(
    RTRIM
    (
        (   SELECT N', ' + CAST(column1Name AS varchar(MAX))
            FROM dbo.tableName
            GROUP BY column1Name
            ORDER BY column2Name
            FOR XML PATH (N'') 
        ) 
    )
, 1, 2, N'')

Is there any way to stuff unique values along with sorting based on a different column?

Answer

You can still use the GROUP BY method. The only thing is when you GROUP BY column1Name, there might be multiple value of column2Name, so you need to use aggregate on it, example MIN(column2Name)

SELECT STUFF
(
    RTRIM
    (
        (   SELECT N', ' + CAST(column1Name AS varchar(MAX))
            FROM dbo.tableName
            GROUP BY column1Name
            ORDER BY MIN(column2Name)
            FOR XML PATH (N'') 
        ) 
    )
, 1, 2, N'')

Advertisement

Answer