Reg : Select more than one field using Case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Reg : Select more than one field using Case

Author

Topic

rajeeshmca
Starting Member

3 Posts

Posted - 2009-10-30 : 03:34:09

Hi

Is it possible to select more than one field within a case?

Eg:
Assume a table has 5 fields Field1, Field2, Field3, Field4, Field5

select
case Field5
when 'A' then
Field1, Field2
when 'B' then
Field3, Field4
end

Is this possible?? If so how?

Rajeesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-30 : 03:42:04

Nope Not possible. You have to use the case statement on every one of them


select case Field5 when 'A' then Field1 when 'B' then Field3 end,
       case Field5 when 'A' then Field2 when 'B' then Field4 end

KH
[spoiler]Time is always against us[/spoiler]

rajeeshmca
Starting Member

3 Posts

Posted - 2009-10-30 : 04:01:20

Is there any other way other than using case?

Rajeesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 04:27:03

select Field1, Field2 from your_table where Field5='A'
select Field3, Field4 from your_table where Field5='B'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-30 : 05:08:17

you missed out the UNION ALL


select Field1, Field2 from your_table where Field5='A' UNION ALL
select Field3, Field4 from your_table where Field5='B'

KH
[spoiler]Time is always against us[/spoiler]

rajeeshmca
Starting Member

3 Posts

Posted - 2009-10-30 : 05:48:07

thanks for tht.. this works

Rajeesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 08:40:20

quote:
Originally posted by khtan

you missed out the UNION ALL

select Field1, Field2 from your_table where Field5='A' UNION ALL
select Field3, Field4 from your_table where Field5='B'

KH
[spoiler]Time is always against us[/spoiler]

Thanks

Madhivanan

Failing to plan is Planning to fail

Pradip
Starting Member

32 Posts

Posted - 2009-10-31 : 08:21:57

Hi Rajesh,
Assume a table has 5 fields Field1, Field2, Field3, Field4, Field5

select
case Field5
when 'A' then
Field1, Field2
when 'B' then
Field3, Field4
end
it is possible

like this

select

case Field5
when 'A' then Field1
when 'B' then Field3
end,
case Field5
when 'A' then Field2
when 'B' then Field4
end
end

Is it ok ...

pradipjain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-31 : 09:14:42

quote:
Originally posted by Pradip

Hi Rajesh,
Assume a table has 5 fields Field1, Field2, Field3, Field4, Field5

select
case Field5
when 'A' then
Field1, Field2
when 'B' then
Field3, Field4
end
it is possible

like this

select

case Field5
when 'A' then Field1
when 'B' then Field3
end,
case Field5
when 'A' then Field2
when 'B' then Field4
end
end

Is it ok ...

pradipjain

OP wanted without the use of CASE expression

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources