how to do a NULL check in unpivot in sql

Question

I have the below sql query which I am using on sql server.

select distinct  [Value],Label,Id from(
select distinct [Name], Code, I as Id,I + ':' + [Value] as label, [Value]
from [test].[dbo].[emp]
unpivot
(
  [Value]
  for I in (product, model)
) as dataTable) as t

What I want to have is if any [Value] inside unpivot statement is null, it should return 'unknown' to the nested select statement.

How do I achieve it ?

Update--

//this is wrong sql. Just want to show what is required
select distinct  [Value],Label,Id from(
select distinct [Name], Code, coalesce(I as Id,'unknown'),coalesce(I,'unknown') + ':' + [Value] as label, coalesce([Value],'unknown')
from [test].[dbo].[emp]
unpivot
(
  [Value]
  for I in (product, model)
) as dataTable) as t

Sample data and desired results would help. – Gordon Linoff Aug 07 '20 at 11:30 — Gordon Linoff, Aug 07 '20 at 11:30

Gordon Linoff · Accepted Answer · 2020-08-07T11:35:43.947

You seem to be looking for coalesce():

from (select distinct [Name], Code, I as Id,
             I + ':' + coalesce([Value], 'unknown') as label,
            [Value]
      from [test].[dbo].[emp]
     ) e

I would suggest writing the query as:

I'm not sure what you really want, but I would suggest writing the query as using apply.

select distinct v.id, (v.id + ':' + coalesce(v.[Value], 'unknown')) as label,
       coalesce(v.[Value], 'unknown') as value
from [test].[dbo].[emp] e cross apply
     (values (e.product, 'product'), (e.model, 'model')
     ) v(value, id);

This seems much simpler and probably performs better too.

how to do a NULL check in unpivot in sql

1 Answers1