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".
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