Doubt - Case when inside a Stuff - SQL Server 2012

Asked

Viewed 100 times

1

Guys, how do I put a case when exists within the query below. The result is currently coming out like this:

Customers Drugstore, Soccer, Gelagua

However, I want to put a case when exists if it is in condition, come yes, or no. Example:

Drugstore - Yes, Soccer - Yes, Gelagua - No

select
(REPLACE(REPLACE(REPLACE((STUFF((
   SELECT
      ':::::: ' + CAST(U.USUNOME AS VARCHAR) + '
 ' FROM Usuario U LEFT JOIN FRM_51 F51C ON U.UsuID = F51C.ContaID LEFT JOIN FRM_52 F52P ON F52P.C01 = F51C.C01  LEFT JOIN Tarefa T ON T.TarID = F52P.TarefaID WHERE  F51C.C04 = 3640 AND F51C.C01 = FRM52.C01 FOR XML PATH('')),1,1,1)),'1::::: ',''),':::::: ',''),' &# x0D;
','')) as Clientes 

   from
      usuario
  • Should the information be horizontal even? Couldn’t it be vertical? Drugstore YES Soccer YES ...

  • in the same horizontal

1 answer

0


Dude, I did the query below, see if this helps (I saw that in your query had many Places and this lot of ":" so I preferred to build an example):

select '1' [id], 'a' [texto] into #temp union
select '1' [id], 'b' [texto] union
select '1' [id], 'c' [texto] union
select '1' [id], 'd' [texto] 

select distinct stuff((select ', ' + case when [texto] = 'a' then [texto] + ' - Sim'
                                when texto = 'b' then texto + ' - Nao'
                                else texto + ' - talvez'
                                end
        from #temp t
        where t.id = fora.id for xml path('')),1,2,'')
from #temp fora

The result of this was:

a - Sim, b - Nao, c - talvez, d - talvez
  • ball show @cotiovis. Thank you

Browser other questions tagged

You are not signed in. Login or sign up in order to post.