CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
msbyuva
msbyuva

Reputation: 3625

how to use multiple select statements in stored procedure as columns in Resultset

I have a stored procedure which has multiple sql statments, 

SELECT X,ID FROM TABLE1
SELECT Y,ID FROM TABLE2 
SELECT Z,ID FROM Table3

WHEN I EXECUTE the above statements form procedure My result set should be

enter image description here

ID, X,Y,Z will the columns and 1,10,20,30 will be the values of 1st row , 6, 40, 50, 60 will be the values of second row, ... goes on I am using SQL SERVER 2005

Is it possible to do so ?

Upvotes: 0

Views: 4747

Answers (1)

Laurence
Laurence

Reputation: 10976

If there's only 1 row in each table you can just do the following:

select
    (select X from table1) X,
    (select Y from table2) Y,
    (select Z from table3) Z;

Example SQLFiddle

For your second example, you can merely use join:

select
    t1.id,
    t1.X,
    t2.Y,
    t3.Z
from
    table1 t1
        inner join
    table2 t2
        on t1.id = t2.id
        inner join
    table t3
        on t2.id = t3.id;

If the tables are really place holders for much larger queries, it might read more easily to use with

;with t1 as (
    select id, X from table1
), t2 as (
    select id, Y from table2,
), t3 as (
    select id, Z from table3
) select
    t1.id,
    t1.X,
    t2.Y,
    t3.Z
from
    t1
        inner join
    t2
        on t1.id = t2.id
        inner join
    t3
        on t2.id = t3.id;

Upvotes: 3

Related Questions