CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

MySql Multiple output when using CASE in a SELECT statement

When using case, how can I get two things out in the result?

Eg:

set @x = 1;

select case when UserId = @x 
then
concat('Test'), // This is the part I fail at. It'll output either one, but not more.
userName 
end from my_members;

Upvotes: 2

Views: 1560

Answers (2)

StuartLC
StuartLC

Reputation: 107267

An alternative is to use @x as a filter predicate and then replace both / all the branches of your CASE with a UNION which selectively targets each of the branches, e.g.:

select UserId, concat('Test', userName) as userName
from my_members
WHERE UserId = @x

UNION 

select UserId, userName
from my_members
WHERE UserId <> @x;

SqlFiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You need two case statements:

set @x = 1;

select (case when UserId = @x then concat('Test') end) as col1
       (case when UserId = @x then userName end) as userName
from my_members;

Personally, I do wish that case statements allowed multiple column returns, but that would require a lot of changes to SQL.

Upvotes: 2

Related Questions