CODEWITHSUNDEEP
sqlsql-serversql-server-2017
Eray Balkanli
Eray Balkanli

Reputation: 7960

When there are labels in different columns, how to set a column to show a strict label or null in sql server

I have a table in sql server like below:

Name  Col1  Col2  Col3  Col4 
A     din   gla   gif   cla 
B     gla   cla   gif   din
C     din   gif   cla   null  --gla is missing
D     null  cla   gla   null  --din and gif is missing

It is only important if a "name" has a label in any of its columns or not. What I need is to return a result set in a way that each column represents a defined value, like below:

Name  Col1  Col2  Col3  Col4 
A     din   gla   gif   cla
B     din   gla   gif   cla
C     din   null  gif   cla
D     null  gla   null  cla

I have managed it with a query like:

select name,
       col1 = case when col1='din' or col2='din' or col3='din' or col4='din' then 'din' else null end,
       ...
from table

However, in original there are many columns, not only 4, and this way does not seem like the best to me. I wonder if you can recommend better approaches. Any advise would be appreciated.

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your method is fine. An alternative method would use apply:

select t.name, v.*
from t cross apply
     (select max(case when col = 'din' then 'din' end) as din,
             max(case when col = 'gla' then 'gla' end) as gla,
             . . .
      from (values (col1), (col2), (col3), (col4)) v(col
     ) v;

Upvotes: 1

Related Questions