CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Rahul Gupta
Rahul Gupta

Reputation: 1802

Concatenate multiple column names into 1 column SQL

I am struggling with small problem here. I have a table with 4 columns

Column1 | Column2 | Column3 | Column4
NULL.........NULL.........NULL.........NULL
ABC..........NULL..........XYZ...........NULL


Column 1,2 & 3 may have some value or null, column4 is null
When any of the column1,2 or 3 are not null, I have to update column4 with that column name. I am expecting following result

Column1 | Column2 | Column3 | Column4
NULL.........NULL.........NULL.........NULL
ABC..........NULL..........XYZ...........Column1,Column3


Can anyone guide me how can I achieve this result.
Any help is appreciated. Thanks in advance.

Upvotes: 0

Views: 66

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Use a case to test for null values and return the column name when it is not null. The stuff is there to remove the extra comma you get first in the string.

update YourTable 
set Column4 = stuff(case when Column1 is not null then ',Column1' else '' end +
                    case when Column2 is not null then ',Column2' else '' end +
                    case when Column3 is not null then ',Column3' else '' end, 1, 1, '')

SQL Fiddle

Upvotes: 2

Related Questions