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: 
**Example**

```
 Declare @YourTable Table ([Customer_Number] varchar(50),[order1] varchar(50),[order2] varchar(50),[order3] varchar(50),[order4] varchar(50))
Insert Into @YourTable Values 
 (1,NULL,'X','Y',NULL)
,(2,NULL,'A','B',NULL)
,(3,'V',NULL,'H',NULL)

 Select Customer_Number   
       ,[Order] = IsNull(stuff(
                  IsNull(','+order1,'')
                 +IsNull(','+order2,'')
                 +IsNull(','+order3,'')
                 +IsNull(','+order4,'')
                 ,1,1,''),'')
 From  @YourTable

```

**Returns**

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

```

> 
> **EDIT - IF the "NULL" are strings and NOT NULL Values**
> 
> 
> 

```
Declare @YourTable Table ([Customer_Number] varchar(50),[order1] varchar(50),[order2] varchar(50),[order3] varchar(50),[order4] varchar(50))
Insert Into @YourTable Values 
 (1,'NULL','X','Y','NULL')
,(2,'NULL','A','B','NULL')
,(3,'V','NULL','H','NULL')

 Select Customer_Number   
       ,[Order] = IsNull(stuff(
                  IsNull(','+nullif(order1,'NULL'),'')
                 +IsNull(','+nullif(order2,'NULL'),'')
                 +IsNull(','+nullif(order3,'NULL'),'')
                 +IsNull(','+nullif(order4,'NULL'),'')
                 ,1,1,''),'')
 From  @YourTable

```
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

```