Home Forums SQL Server 2012 SQL Server 2012 - T-SQL SQL Distinct comma delimited list RE: SQL Distinct comma delimited list

  • July 24, 2013 at 3:57 pm

    #1635561

    Erland Sommarskog (7/24/2013)

    SELECT substring(list, 1, len(list) - 1)

    FROM (SELECT list =

    (SELECT DISTINCT name + ','

    FROM sys.objects

    ORDER BY name + ','

    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')) AS T

    Yes, somewhat unwieldy and not fully intuitive. On the other hand, this is guaranteed to work as intended, which is not true for "SELECT @x = @x + col".

    Why is the SELECT @x=@x+ method not guaranteed to work?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001