Conditionally select from cte

Question

Post reply

Conditionally select from cte

irobertson

Hall of Fame

Points: 3695
More actions
June 8, 2009 at 9:13 am

#132738

Hi,

Can I conditionally select from a cte? I'm not sure it's possible.

E.g.

declare @input_variable int

;with cte as (

select Col1

, Col2

, Col3

, Col4

from MyTable

)

if @input_variable = 1

begin

select sum(Col1) from cte

end

if @input_variable = 2

begin

select sum(Col2) from cte

end

etc...

Thanks, Iain

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33556 More actions · Answer 1

Dave Ballantyne

SSC-Dedicated

Points: 33556

June 8, 2009 at 9:15 am

#1006111

You cant do that. A cte's scope is a single statement.

Maybe a view would better suit you ?

Clear Sky SQL
My Blog[/url]

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 2

Maybe this?

with cte as (



select Col1

, Col2

, Col3

, Col4

from MyTable

)

select case when @input_variable = 1  then sum(Col1)

            when @input_variable = 2  then sum(Col2)

       end

from cte

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 3

irobertson (6/8/2009)

Hi,

Can I conditionally select from a cte? I'm not sure it's possible.

E.g.

declare @input_variable int

;with cte as (

select Col1

, Col2

, Col3

, Col4

from MyTable

)

if @input_variable = 1

begin

select sum(Col1) from cte

end

if @input_variable = 2

begin

select sum(Col2) from cte

end

etc...

Thanks, Iain

How about this ?

drop table MyTable



create table Mytable

(Col1 int, Col2 Int, COl3 int, Col4 int)

insert into MyTable

select 1,2,3,4 UNION ALL

select 1,2,3,4 UNION ALL

select 1,2,3,4 UNION ALL

select 1,2,3,4 UNION ALL

select 1,2,3,4 UNION ALL

select 1,2,3,4 UNION ALL

select 1,2,3,4

declare @input_variable int

set @input_variable = 2

;with cte as (

select case when @input_variable = 1 then Col1

			when @input_variable = 2 then Col2

			when @input_variable = 3 then Col3

			when @input_variable = 4 then Col4

		end as Col

from MyTable

)

Select Sum(Col) from cte