how to make into single sql statement

Posted by Oswaldlily under Sql Server on 10/11/2011 | Points: 10 | Views : 1081 | Status : [Member] | Replies : 8
if type='a'
begin
if color='r'
begin
select column_one as [column one] from Tablename
end
else if color='b'
begin
select column_two as [column two] from Tablename
end
else if color='g'
Begin
select column_three as [column three] from tablename
end
end

How to make this into single sql statement




Responses

Posted by: Oswaldlily on: 10/11/2011 [Member] Starter | Points: 25

Up
0
Down
I tried using in following format
select case when color='r' then column_one when color='b' then column_two when color='g' then column_three end as [column one] from TAblename

Here i can mention oly one column name after end statement. (i.e) [column one]
I need for [column two] when color='b'
and [column three] when color='g'
how to do this

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Walia_jagwinder on: 10/11/2011 [Member] Starter | Points: 25

Up
0
Down
Check the below example:


SELECT col1, col2, col3, CASE WHEN condition THEN CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation1 ELSE calculation2 END ELSE CASE WHEN condition2 THEN calculation3 ELSE calculation4 END END ELSE CASE WHEN condition1 THEN CASE WHEN condition2 THEN calculation5 ELSE calculation6 END ELSE CASE WHEN condition2 THEN calculation7 ELSE calculation8 END END END AS 'calculatedcol1', col4, col5 -- etc FROM table


One person's data is another person's program.

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 10/11/2011 [Member] Starter | Points: 25

Up
0
Down
Hi You can write some thing like this
select  

CASE when color='r' Then column_one Else NULL end as [column one],
CASE when color='g' Then column_two Else NULL end as [column two],
CASE when color='b' Then column_three Else NULL end as [column three],
from Tablename


Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 10/11/2011 [Member] Starter | Points: 25

Up
0
Down
@Ndebata
your code wrks with diff column name as i expect..good..but at a time it displays all 3 columns
I need in follown condition
when color='r' rest 2 column should be invisible
when color='g' rest 2 coloumn shd be invisbile
when color='b' rest 2 column shd be invisible


Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 10/11/2011 [Member] Starter | Points: 25

Up
0
Down
Dear Friend,
Use the null check to hide where you are using this query.


Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 10/11/2011 [Member] Starter | Points: 25

Up
0
Down
If i uncheck that null,oly datas wont appear..but column ll be visibl right??


Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.biswas on: 10/12/2011 [Member] Platinum | Points: 25

Up
0
Down
Try this

Declare @t table(Col1 Varchar(20),Col2 Varchar(20),Col3 Varchar(20))

Insert into @t Select 'Col1 Value','Col2 Value','Col3 Value'

Declare
@type varchar(10) = 'a'
,@color varchar(10) = 'b'

Select
X=
Case
When @type = 'a'
Then Case
When @color = 'r' Then (Select Col1 From @t)
When @color = 'g' Then (Select Col2 From @t)
When @color = 'b' Then (Select Col3 From @t)
End
End


Best Regards,
Niladri Biswas

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Niladri.biswas on: 10/12/2011 [Member] Platinum | Points: 25

Up
0
Down
Another solution using IIF and Choose of Denali (Sql 11)

Declare @t table(Col1 Varchar(20),Col2 Varchar(20),Col3 Varchar(20))
Insert into @t Select 'Col1 Value','Col2 Value','Col3 Value'

Declare 

@type varchar(10) = 'a'
,@color varchar(10) = 'b'

;With CTE AS
(
Select [Key] = 'r', [Value] = 1 Union All
Select [Key] = 'g', [Value] = 2 Union All
Select [Key] = 'b', [Value] = 3
)
Select
IIF(@type = 'a',
Choose(
x.Value
,(Select Col1 From @t)
,(Select Col2 From @t)
,(Select Col3 From @t)
),Null)
From (
Select [Value]
from CTE
WHERE [Key] = @color
) X


Best Regards,
Niladri Biswas

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response