Multiple Select Statements in 1 Query

i'm going to guess that there's no way to do this in T-SQL (or any
other SQL implementation), but I figured I'd ask around:
basically, what I'm looking to do is replace this union statement::
SELECT intX
FROM Table1
UNION
SELECT intY
FROM Table1
with a syntactical shortcut:
SELECT intX
SELECT intY
FROM Table1
The need for this shortcut is that my FROM clause is very complex, and
copying it multiple times is messy (and frankly, I'm in no mood to
create a View given the number of non-unique column names in the joined
tables). Anyone have any suggestions?scottstein@.gmail.com wrote:
> i'm going to guess that there's no way to do this in T-SQL (or any
> other SQL implementation), but I figured I'd ask around:
> basically, what I'm looking to do is replace this union statement::
> SELECT intX
> FROM Table1
> UNION
> SELECT intY
> FROM Table1
> with a syntactical shortcut:
> SELECT intX
> SELECT intY
> FROM Table1
> The need for this shortcut is that my FROM clause is very complex, and
> copying it multiple times is messy (and frankly, I'm in no mood to
> create a View given the number of non-unique column names in the joined
> tables). Anyone have any suggestions?
In SQL Server 2005 you can use a CTE. You'll have to assign some unique
column names though:
WITH T1 (intx, inty)
AS
(SELECT ... /* your complex logic here */ )
SELECT intx
FROM T1
UNION
SELECT inty
FROM T1 ;
There may be a better solution by changing the complex part of your
query that you didn't post.
David Portas
SQL Server MVP
--|||if are aren't on 2K5 yet, you can cross join with a sequence table,
like this:
select case when t=1 then expr1 else expr2 end
from(
-- your really complex expression goes here
select 'expr1' expr1, 'expr2' expr2)c
cross join
(select 1 t union all select 2) t
expr1
expr2
(2 row(s) affected)|||Thanks David + Alexander, I appreciate your responses. Unfortunately,
CTE has the same problem as the view -- unique column names. I haven't
even seen 2005 yet, so that isn't an option for now. Again, this
problem isn't unbearable, but it just makes my code a little messy.