如何在sql中执行空签入unpivot

kokeuurv 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(269)

下面是我在SQLServer上使用的sql查询。

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

我想要的是如果有的话 [Value] 如果unpivot语句内部为null，则应将“unknown”返回到嵌套的select语句。
我如何实现它？
更新--

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

sql sql-server sql-tuning

来源：https://stackoverflow.com/questions/63300894/how-to-do-a-null-check-in-unpivot-in-sql

1条答案

按热度按时间

jqjz2hbq1#

你好像在找 coalesce() :

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

我建议将问题写为：
我不确定您真正想要什么，但我建议您使用 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);

这看起来简单得多，而且可能性能也更好。

赞(0）回复(0）举报 2021-07-16

我来回答

如何在sql中执行空签入unpivot

1条答案

相关问题

热门标签

最新问答