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.
This is a bit unpleasant, it needs to be in a subquery to remove the trailing comma efficiently (though you could use a CTE):

```
SELECT 
    Customer_number, 
    SUBSTRING([Order], 0, LEN([Order])) AS [Order]
FROM(
    SELECT 
        Customer_number,
        COALESCE(order1+',', '') +
        COALESCE(order2+',', '') +
        COALESCE(order3+',', '') +
        COALESCE(order4+',', '') AS [Order]
    FROM
        OrderTable) AS SubQuery

```