SQL Server Performance

Output

Discussion in 'T-SQL Performance Tuning for Developers' started by alex, Jan 27, 2005.

  1. alex New Member

    Hello, everybody.

    What is the way to see the outputs of several select statements side by side?
    For example, if I have:
    Select a from table1 where a in (1, 2, 3, 4)
    Select a from table2 where a in (1, 3)
    Select a from table3 where a in (2, 3)

    then in the output it would look like:

    A(from table1) A(from table2) A(from table3)
    11NULL
    2NULL2
    333
    4NULLNULL

    Thanks a lot.
  2. Madhivanan Moderator

    Something similar to this




    Declare @table1 table(a int)
    Declare @table2 table(a int)
    Declare @table3 table(a int)
    insert into @table1 values (1)
    insert into @table1 values (2)
    insert into @table1 values (3)
    insert into @table2 values (1)
    insert into @table2 values (2)
    insert into @table2 values (3)
    insert into @table3 values (1)
    insert into @table3 values (2)
    insert into @table3 values (3)


    Select "A(from Table1)"=(Select a from @table1 where a =1),
    "A(from Table2)"=(Select a from @table2 where a =2),
    "A(from Table3)"=(Select a from @table3 where a =3)




    Madhivanan
  3. alex New Member

    Thanks for your respond, but it is not what I need. In the output 1 from the first table should stay in the same row with 1 from the second and third statement and so forth. In case that in the second or third statement 1 is missing - in the output stay NULL.

    This is how the result should look:

    Table1 Table2 Table3
    1 NULL 1
    2 2 NULL
    3 3 3

    Thanks.
  4. Madhivanan Moderator

    Try this



    Declare @table1 table(a int)
    Declare @table2 table(a int)
    Declare @table3 table(a int)
    Declare @temp table (tableName varchar(30),a int)
    insert into @table1 values (1)
    insert into @table1 values (2)
    insert into @table1 values (3)
    insert into @table2 values (1)
    insert into @table2 values (2)
    insert into @table2 values (3)
    insert into @table3 values (1)
    insert into @table3 values (2)
    insert into @table3 values (3)

    insert into @temp
    Select 'T1',a from @table1 where a in(1,2,3)
    union all
    Select 'T2',a from @table1 where a in(2,3)
    union all
    Select 'T3',a from @table1 where a in(1,3)


    Select "A(from Table1)"=(Select a from @temp where TableName='T1' and a=T.a),
    "A(from Table2)"=(Select a from @temp where TableName='T2' and a=T.a),
    "A(from Table3)"=(Select a from @temp where TableName='T3' and a=T.a) from @temp T group by a





    Madhivanan

Share This Page