Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SELECT list block with CASE? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, March 04, 2013 5:49 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, October 31, 2013 10:50 AM Points: 83, Visits: 295
 is something like this possible?SELECTCASE WHEN (@X = 1) THEN COLUMN1, COLUMN2, COLUMN3ELSE COLUMN4, COLUMN5, COLUMN6,ENDFROM TABLE1
Post #1426200
 Posted Monday, March 04, 2013 6:04 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 7:32 AM Points: 6,274, Visits: 12,089
 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 SampleTableCROSS 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1426202
 Posted Monday, March 04, 2013 6:12 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 7:39 AM Points: 2,052, Visits: 5,106
 One simple way is to use a if block:`If @x=1 Select column1, column2, column3 from MyTableElse 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 col3from 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 05, 2013 2:39 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, December 05, 2013 1:58 PM Points: 159, Visits: 877
 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 SampleTableCROSS APPLY ( SELECT COLUMN1 = 'COLUMN1', COLUMN2 = 'COLUMN2', COLUMN3 = 'COLUMN3' WHERE @X = 1 UNION ALL SELECT COLUMN4, COLUMN5, COLUMN6 WHERE @X <> 1 ) xChrisM@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 05, 2013 7:57 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, November 26, 2013 10:25 PM Points: 114, Visits: 900
 Maybe, you can use a dynamic query`DECLARE @X bit, @strSQL nvarchar(2000)SET @X = 1SET @strSQL =' SELECT ' + CASE WHEN @X = 1 THEN ' COLUMN1,COLUMN2,COLUMN3' ELSE ' COLUMN4,COLUMN5,COLUMN6' END + ' FROM TABLE1 ' EXEC (@strSQL) `
Post #1427129
 Posted Wednesday, March 06, 2013 1:04 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 7:32 AM Points: 6,274, Visits: 12,089