May 20, 2002 at 4:17 am
Hi, I'm trying to rationalise some of my proceedures and I want to consolidate some case statements, but cant seem to create valid TSQL, does anyone know if this is valid ?
Select Case myColumn
When 1 then
Column1, Column2, Column3
Else
Column4, Column5, Column6
End
From MyTable
I want to stop repeating the case statements for each for the columns, and get away from :
Select Case myColumn
When 1 then
Column1
Else
Column4
End
Many Thanks
Case myColumn
When 1 then
Column2
Else
Column4
End
Case myColumn
When 1 then
Column3
Else
Column5
End
From MyTable
May 20, 2002 at 4:48 am
Unfortunately unless you are going to concatinate each column together case only allows the selection of one output item. However you could do the following.
In procedure
IF myColumn = 1
BEGIN
SELECT Col1, COl2, COl3 FROM MyTable
END
ELSE
BEGIN
SELECT Col4, Col5, Col6
END
However it looks to me like you have a column value that determines you column output so you are right back to what you want to stop
CASE myCol WHEN 1 then Col1 ELSE Col4 END,
CASE myCol WHEN 1 then Col2 ELSE Col5 END,
CASE myCol WHEN 1 then Col3 ELSE Col6 END
No other option if you want to kee seperate columns.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 20, 2002 at 9:43 am
Agree. There isn't a way to do this (other than dynamic sql, which I don't recommend). Besides, that's what CTRL-C, CTRL-V are for 🙂
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply