COALESCE on a single table [duplicate]

Question

I have the below table and i would like to get the below result with using the COALESCE . Any help will be appreciated.

Thanks sam

Answer 1

This is the generally accepted standard for how to concatenate values contained over multiple rows.

SELECT
    ID,
    LEFT(brands, LEN(brands) -2) brands
FROM 
    (
        SELECT DISTINCT ID, 
            (
                SELECT
                    brand + ', ' [text()]
                FROM 
                    myTable brands 
                WHERE 
                    brands.id = ids.id
                FOR XML PATH ('')
            ) brands
        FROM
            myTable ids
        GROUP BY 
            ID
    ) m

Of course adding coalesce (null, value) for any given value will meet your criteria for using the coalesce function but this is doesn't add functionality or leverage the coalesce function in a useful way.