Sql server在when语句中显示字符串(Sql server display string in when statement)

Sql server在when语句中显示字符串(Sql server display string in when statement)

我有以下查询

select (case when object like'%service%' then (object) when class like '%service%' then (class)end) From db group by (case when object like '%service%' then object when object_class like '%service%' then object_class end)

此查询检查每列的值是否包含'service'而不是显示数据，我需要代替仅显示数据，数据和字符串 有点when object like'%service%' then (object),'string'

String将是一个变量，在结果中存储在未知列中

column1 column2 objet1 mystring objet2 mystring objet3 mystring

I have the following query

select (case when object like'%service%' then (object) when class like '%service%' then (class)end) From db group by (case when object like '%service%' then object when object_class like '%service%' then object_class end)

this query check if value of each column contains 'service' than display the data, i need instead of data only display, data and string Some thing like when object like'%service%' then (object),'string'

String will be a variable which is stored in unknown column at results

column1 column2 objet1 mystring objet2 mystring objet3 mystring

最满意答案

CASE语句只返回一个值 - 您不能使用它返回多个列。

您可以在每列中重复case语句：

select case when object like '%service%' then object when class like '%service%' then class end as column1, case when object like '%service%' then 'string' when class like '%service%' then 'string' end as column2 FROM ...

或做一个UNION ：

select object as column1, 'string' as column2 WHERE object like '%service%' FROM ... UNION ALL select class as column1, 'string' as column2 WHERE class like '%service%' FROM ...

请注意，如果有任何记录，其中object和class 都包含字符串service ，则结果将不同。 UNION将返回两条记录， CASE将只包含一条（首先匹配object ）。

The CASE statement only returns one value - you cannot use it to return multiple columns.

You can either repeat the case statement in each column:

select case when object like '%service%' then object when class like '%service%' then class end as column1, case when object like '%service%' then 'string' when class like '%service%' then 'string' end as column2 FROM ...

or do a UNION:

select object as column1, 'string' as column2 WHERE object like '%service%' FROM ... UNION ALL select class as column1, 'string' as column2 WHERE class like '%service%' FROM ...

Note that the results will be different if there are any records where object and class both contain the string service. The UNION will return two records, the CASE will only include one (matching on object first).