Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 3995
Home > Forums > Sql Server >

how to make into single sql statement

Posted by Oswaldlily under Sql Server on 10/11/2011 | Points: 10 | Views : 2227 | Status : [Member] | Replies : 8
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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



Reply | Reply with Attachment
Alert Moderator 

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

Latest Posts