Using distinct in comma seperated list Can someone Explain why i get only last row ?

where as when i remove DISTINCT i can able to achieve full rows.

DECLARE @liststr VARCHAR(MAX) = ''
;WITH COMMA (name)
AS
(
        SELECT 'a' 
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT DISTINCT @liststr = @liststr+',' + name FROM comma
SELECT @liststr as Result
In the original, unedited question you use UNION instead of UNION ALL in your CTE so the DISTINCT is unnecessary. Consider the following query:

DECLARE @liststr VARCHAR(MAX) = '';
WITH COMMA (name)
AS
(
        SELECT 'a' 
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT @liststr = @liststr+',' + name FROM (SELECT DISTINCT name FROM comma) AS comma;
SELECT @liststr as Result;


Here is the query plan for the above:



The sort occurs before the string concatenation, which is what you want. The result is ",a,b,d".

Now consider this query:

DECLARE @liststr VARCHAR(MAX) = '';
WITH COMMA (name)
AS
(
        SELECT 'a' 
    UNION ALL
        SELECT 'b'
    UNION ALL
        SELECT 'a'
    UNION ALL
        SELECT 'd'
)
SELECT DISTINCT @liststr = @liststr+',' + name FROM comma;
SELECT @liststr as Result;


Here is the query plan for the above:



The sort occurs after the string concatenation, which is not what you want. The result is ",d".

I don't recommend using either query. I don't know what we are seeing here is defined behavior or if you'll consistently get the result that you want from the first query. It's possible that you'll end up with a query that works now but is broken by a future update of SQL Server. If it's important for you to get consistent results for this query you may want to consider a different method of string concatenation.