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
– Sormita Chakraborty, Commented Mar 23, 2015 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
– Redadublex, Commented Mar 23, 2015 at 15:35
a CASE statement can only return 1 column, stackoverflow.com/questions/2072721/… — Pupa Rebbe
– Pupa Rebbe, Commented Mar 23, 2015 at 15:38
Do you need the data and string returned in a single column, or as two different columns? — Tab Alleman
– Tab Alleman, Commented Mar 23, 2015 at 15:43

D Stanley · Accepted Answer · 2015-03-23 15:51:17Z

2

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

answered Mar 23, 2015 at 15:51

D Stanley

153k12 gold badges190 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Redadublex Over a year ago

I solved it by using you first answer and removing group by statement because it was useless

Thorsten Kettner · Accepted Answer · 2015-03-23 16:08:43Z

1

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;

edited Mar 23, 2015 at 16:08

answered Mar 23, 2015 at 15:56

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Sql server display string in when statement

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related