How to concatenate columns without null value in sql

Amitesh Verma

9y1.2k

Hi coders,

I have a table in which i need to concatenate columns but i dont want NULL value in it. Example:

ProductTable

Customer_Number Product1 Product2 Product3 Product3

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 product only like this:

Customer_Number Product

1 X,Y

2 A,B

3 V, H

I already try Coalease, Stuff, ISNULL, NULLIF but in all i m getting like below

Customer_Number Product

1 NULL,X,Y,NULL

2 NULL,A,B,NULL

3 V, NULL,H,NULL

This is the code i used:

Select Customer_number, ISNULL(IFNULLIF(Product1,' ')+',','')+

ISNULL(IFNULLIF(Product2,' ')+',','')+

ISNULL(IFNULLIF(Product3,' ')+',','')+

ISNULL(IFNULLIF(Product4,' ')+',','')

As Product from productTable

Any help will be appriciated !!!

3 Replies

Know the answer? Post it — somebody with the same question will find it here.