Question: This question is asked many time here but i am not getting the proper output as i am expecting:

I have a table in which i need to concatenate columns but i don't want NULL value in it. I want it in sql server 2008 concate function does not work in 2008.

Example:
OrderTable

```
 Customer_Number   order1   order2  order3   order4 
    1               NULL      X       Y       NULL   
    2               NULL      A       B       NULL
    3                V       NULL     H       NULL 

```

Now want i want is the data in concatenated manner for order only like this:

```
Customer_Number     Order
  1                 X,Y
  2                 A,B
  3                 V,H

```

This is the code i used 

```
 Select Customer_number, ISNULL(NULLIF(order1,' ')+',','')+
 ISNULL(NULLIF(order2,' ')+',','')+ 
 ISNULL(NULLIF(order3,' ')+',','')+
 ISNULL(NULLIF(order4,' ')+',','')
 as Order from Ordertable

```

I got the below output

```
Customer_Number      Order
  1                NULL,X,Y,NULL
  2                NULL,A,B,NULL
  3                V,NULL,H,NULL

```

I already try Coalesce, Stuff, ISNULL, NULLIF but all have same result 

Thanks in advance !!!

Answer: 
Another variation, for fun and profit, demonstrating the `FOR XML` trick to concatenate values pre-SQL Server 2012.

```
SELECT Customer_Number, STUFF(
    (SELECT ',' + order1, ',' + order2, ',' + order3, ',' + order4 FOR XML PATH('')), 
    1, 1, ''
)

```

This is slight overkill for a constant number of columns (and not particularly efficient), but an easy to remember pattern for concatenation. Also, it shows off `STUFF`, a function any SQL developer should learn to love.
You were on the right track with the Coalesce, or IsNull. Instead of trying to track the length and use substring, left or stuff, I just used a replace to remove the trailing comma that would show up from the coalesce.

```
Select Customer_number, 
    Replace(
        Coalesce(Order1 + ',','')+
        Coalesce(Order2 + ',','')+
        Coalesce(Order3 + ',','')+
        Coalesce(Order4 + ',','')
     +',',',,','') --Hack to remove the Last Comma
 as [Order] from Ordertable

```