Sql server display string in when statement

Question

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

can you please clarify? if the column contains '%service%' then you want to display the data in that column for that particular row? — Sormita Chakraborty, Mar 23 '15 at 15:33
Yes displaying data is already solved, but i need to display data and a string, like if we do (select object , 'string')from .... — Redadublex, Mar 23 '15 at 15:35
a `CASE` statement can only return 1 column, http://stackoverflow.com/questions/2072721/how-can-i-select-multiple-columns-within-a-case-when-on-sql-server — Pupa Rebbe, Mar 23 '15 at 15:38
Do you need the data and string returned in a single column, or as two different columns? — Tab Alleman, Mar 23 '15 at 15:43
The changed query lacks an end for the first CASE. And why do you GROUP BY without doing any aggregation? All this actually does is SELECT DISTINCT, only obfuscated. And why all those parentheses? — Thorsten Kettner, Mar 23 '15 at 16:01
@ThorstenKettner you were on right, group by was useless, result worked after removing it — Redadublex, Mar 23 '15 at 16:08

score 2 · Accepted Answer · answered Mar 23 '15 at 15:51

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).

I solved it by using you first answer and removing group by statement because it was useless — Redadublex, Mar 23 '15 at 16:08

Thorsten Kettner · Answer 2 · 2015-03-23T16:08:43.707

So in case one of the columns contains the word 'service' you display the first match as column1. And you want to display 'string' in column2 in case you display something in column1.

EDIT: I've added a GROUP BY clause on the column1, to show you how to aggregate here:

select 
  column1, 
  case when column1 is not null then 'string' end as column2,
  min(some_other_column)
from
(
  select 
    case 
      when object like '%service%' then object 
      when  class like '%service%' then class
    end as column1,
    some_other_column
  from mytable
) mydata
group by column1;

Sql server display string in when statement

2 Answers2