How to avoid null != null in t-sql

Question

I have a table, containing two columns:

Column1  Column2
a        g
b        null
a        e
null     g
d        null
...      ...

For every Column1 I want to get a semicolon separated list of Column2 values, so here is my code:

select
    coalesce(t1.Column1, 'Empty') as col1,
    (
        select
            t2.Column2 + '; '
        from
            table as t2
        where
            t2.Column1 = t1.Column1
        for xml path(N'')
    ) as col2list
from
    table as t1

Of course, when the t1.Column1 is null, the inner query doesn't return anything, cause null is not equal to null. But I need to get these values of Column2 for cases when Column1 is null and display them against the 'Empty' word. Any idea how it could be done?

Thanks in advance.

Answer 1

try

select
    coalesce(t1.Column1, 'Empty') as col1,
    (
        select
            isnull(t2.Column2,'') + '; '
        from
            table as t2
        where
            isnull(t2.Column1,'') = isnull(t1.Column1,'')
        for xml path(N'')
    ) as col2list
from
    table as t1

Answer 2

Add another condition to your where clause:

select
    coalesce(t1.Column1, 'Empty') as col1,
    (
        select
            t2.Column2 + '; '
        from
            table as t2
        where
            t2.Column1 = t1.Column1
        or 
            (t2.Column1 is null and t1.Column1 is null)
        for xml path(N'')
    ) as col2list
from
    table as t1

Answer 3

Try this sets null values first so that you can pair them up.