Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT list block with CASE? Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 5:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 10:18 AM
Points: 83, Visits: 305
is something like this possible?

SELECT

CASE WHEN (@X = 1)
THEN
COLUMN1,
COLUMN2,
COLUMN3
ELSE
COLUMN4,
COLUMN5,
COLUMN6,
END

FROM TABLE1

Post #1426200
Posted Monday, March 4, 2013 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
No, but you can cheat:

DECLARE @X TINYINT = 0

;WITH SampleTable AS (
SELECT SOMECOLUMNS = 'SOMECOLUMNS', COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3',
COLUMN4 = 'COLUMN4', COLUMN5 = 'COLUMN5', COLUMN6 = 'COLUMN6'
)
SELECT SOMECOLUMNS, x.*
FROM SampleTable
CROSS APPLY (
SELECT COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3' WHERE @X = 1
UNION ALL
SELECT COLUMN4, COLUMN5, COLUMN6 WHERE @X <> 1
) x



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1426202
Posted Monday, March 4, 2013 6:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:19 AM
Points: 2,112, Visits: 5,477
One simple way is to use a if block:
If @x=1
Select column1, column2, column3 from MyTable
Else
Select column4, column5, column6 from MyTable

If you insist of doing just one select statement, then if you don't mind that the columns will have the same name regardless of the @x's value, you can use this code:

select case when @x = 1 THEN column1 else column4 end as col1,
case when @x = 1 THEN column2 else column5 end as col2,
case when @x = 1 THEN column3 else column6 end as col3
from MyTable

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1426203
Posted Tuesday, March 5, 2013 2:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:19 PM
Points: 187, Visits: 1,153
DECLARE @X TINYINT = 0

;WITH SampleTable AS (
SELECT SOMECOLUMNS = 'SOMECOLUMNS', COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3',
COLUMN4 = 'COLUMN4', COLUMN5 = 'COLUMN5', COLUMN6 = 'COLUMN6'
)
SELECT SOMECOLUMNS, x.*
FROM SampleTable
CROSS APPLY (
SELECT COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3' WHERE @X = 1
UNION ALL
SELECT COLUMN4, COLUMN5, COLUMN6 WHERE @X <> 1
) x


ChrisM@Work, please help educate me. There is something very simple here I don't understand.

How does CROSS APPLY join SampleTable to x?
Post #1427042
Posted Tuesday, March 5, 2013 7:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 141, Visits: 970
Maybe, you can use a dynamic query
DECLARE @X bit, @strSQL nvarchar(2000)
SET @X = 1

SET @strSQL =' SELECT ' + CASE WHEN @X = 1 THEN ' COLUMN1,COLUMN2,COLUMN3'
ELSE ' COLUMN4,COLUMN5,COLUMN6' END
+ ' FROM TABLE1 '

EXEC (@strSQL)

Post #1427129
Posted Wednesday, March 6, 2013 1:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
jshahan (3/5/2013)
DECLARE @X TINYINT = 0

;WITH SampleTable AS (
SELECT SOMECOLUMNS = 'SOMECOLUMNS', COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3',
COLUMN4 = 'COLUMN4', COLUMN5 = 'COLUMN5', COLUMN6 = 'COLUMN6'
)
SELECT SOMECOLUMNS, x.*
FROM SampleTable
CROSS APPLY (
SELECT COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3' WHERE @X = 1
UNION ALL
SELECT COLUMN4, COLUMN5, COLUMN6 WHERE @X <> 1
) x


ChrisM@Work, please help educate me. There is something very simple here I don't understand.

How does CROSS APPLY join SampleTable to x?


The second to last line of my sig is a link to Paul White's famous APPLY articles. Well worth a read. APPLY without a table reference simply means calculate this and affects, and returns results to, the "current row".


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1427220
Posted Wednesday, March 6, 2013 6:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:19 PM
Points: 187, Visits: 1,153
Thanks for the response and the reference. I've got to wrap my brain around this. Been driving me nuts.
Post #1427336
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse