none
how to merge two tables by sql query

    Question

  • hello to all


    i want to merge two tables

    my query is

    declare @aa as varchar(255);
     declare @aa1 as varchar(255);
    declare @aa3 as varchar(255);
     set @aa='1073741824'
     set @aa1=( SELECT isnull(sum([size1]),0) FROM [treeview] WHERE ([nodetype] = 'file'))
     declare @aa2 as varchar(255);
     set @aa2=@aa+','+@aa1
    set @aa3='Free Space'+','+'Used Space'
     (select data as Data1from dbo.split(@aa2,','))
    (select data as Data2from dbo.split(@aa3,','))


    and my output is look like:

    Data1

    1073741824

    625055.20

     

    Data2


    Free Space

    Used Space

     
    but i want my output like

    Data1                Data2

    1073741824     Free Space

    625055.20       Used Space

    Monday, February 21, 2011 9:28 AM

Answers

  • OR

    with t1
    as
    (
    select id,data from dbo.split(@aa2,',')
    ),  t2
    as
    (
    select id,data from dbo.split(@aa3,',')
    )
     select t1.id,t1.data as Data1,t2.data as Data2
    from t1 join t2 on  t1.id=t2.id


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by nikijain Monday, February 21, 2011 10:59 AM
    Monday, February 21, 2011 10:47 AM
    Answerer

All replies

  • Add some field as  ID , could be a number generated by ROW_NUMBER finction and then JOIN two sets ON a.ID=b.ID
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, February 21, 2011 9:41 AM
    Answerer
  • thanks for reply but i am not getting what u are saying..how can i join when i have two statements without in any table. (select id,data as data1 from dbo.split(@aa2,',')) (select id,data as data2 from dbo.split(@aa3,',')) tell me how could i create the table and join it
    Monday, February 21, 2011 10:14 AM
  • Well, provide sample data + an expected result and  we will try to provide better suggestion


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, February 21, 2011 10:23 AM
    Answerer
  • see i have declare four variables

    declare @aa as varchar(255);

    declare @aa1 as varchar(255);
    declare @aa2 as varchar(255);

    declare @aa3 as varchar(255);

     

    after that i have set values in aa

    set @aa='1073741824'

     

    i am retriving some data from database table in variable @aa1

    set @aa1=( SELECT isnull(sum([size1]),0) FROM [treeview] WHERE ([nodetype] = 'file'))

    after that in variable @aa2 i am recieving aa and aa1 by comma separated

    and finally i am hardcoding in the variable

    set @aa3='Free Space'+','+'Used Space'

     

    and lastly i am firing two queries

     

     (select id,data as Data1 from dbo.split(@aa2,','))
    (select id,data as Data2 from dbo.split(@aa3,','))

    selecting all and debugging  i am getting the output like this

    firstly

    id     Data1

    1       1073741824

    2        60441811.20     (this i am calculating from another table)

     

    Secondly

     

    id     Data2

    1       Free Space

    2        Used Space

     

     

    Now what i wants , i want this firstly and Secondly with in same table like

     

        id           Data1                Data2

         1          1073741824     Free Space

         2           625055.20       Used Space

     

    Plz help   me

     


     

     

    Monday, February 21, 2011 10:38 AM
  • Sorry ,not tested

     select t1.id,t1.data as Data1,t2.data as Data2 from dbo.split(@aa2,',') as t1
    join (select id,data as Data2 from dbo.split(@aa3,',')) as t2
    on t1.id=t2.id


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, February 21, 2011 10:42 AM
    Answerer
  • OR

    with t1
    as
    (
    select id,data from dbo.split(@aa2,',')
    ),  t2
    as
    (
    select id,data from dbo.split(@aa3,',')
    )
     select t1.id,t1.data as Data1,t2.data as Data2
    from t1 join t2 on  t1.id=t2.id


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by nikijain Monday, February 21, 2011 10:59 AM
    Monday, February 21, 2011 10:47 AM
    Answerer
  • o thats great...thanks a lot
    Monday, February 21, 2011 11:00 AM