sql多个查询结果合并为一个表的问题

[kuangzi]

select name col1 from syscolumns where id=object_id('TableName1')

id
username
status

select name col2  from syscolumns where id=object_id('TableName2')

uid
password
area
street
num

查询出了两个表的所有列（列的数目不一样），怎样把两次的查询结果合并为一个表，使查询结果变为表的两个字段。

id                            uid
username                      password
status                        area
                              street
                              num

请教各位大神

[骑着蜗牛去爬山]

union all

[卖水果的net]

with m as (
select row_number() over(order by id) rn , name ,'' x 
    from syscolumns where id = 3
union all
select row_number() over(order by id) rn , '' x, name 
    from sysobjects where id = 55
)
select rn,MAX(name) c2, MAX(x) c3 from m group by rn 


rn                   c2                            c3
-------------------- ----------------------------- ---------------
1                    rsid                          sysiscols
2                    rscolid                                       
3                    hbcolid                                       
4                    rcmodified                                    
5                    ti                                            
6                    cid                                           
7                    ordkey                                        
8                    maxinrowlen                                   
9                    status                                        
10                   offset                                        
11                   nullbit                                       
12                   bitpos                                        
13                   colguid                                       
14                   dbfragid                                      

(14 行受影响)

[kuangzi]

union all 之后只有一列啊，要求把查询结果作为两列输出。

[二月十六]

;WITH tempa AS (
SELECT  name col1,ROW_NUMBER()OVER(ORDER BY name) num
FROM    syscolumns
WHERE   id = OBJECT_ID('TableName1')
),tempb AS (
SELECT  name col1,ROW_NUMBER()OVER(ORDER BY name) num
FROM    syscolumns
WHERE   id = OBJECT_ID('TableName2')
)
SELECT  tempa.col1 ,
        tempb.col1
FROM    tempa
        FULL JOIN tempb ON tempb.num = tempa.num

[骑着蜗牛去爬山]

cross apply

[Pei你看雪 .]

引用 5 楼 骑着蜗牛去爬山 的回复:

cross apply

只有一个表怎么办

[文盲老顾]

select * from (select name,colorder,object_name(id) as tbname from syscolumns where id in (object_id('pub_articles'),object_id('pub_bids'))) a pivot(max(name) for tbname in (pub_articles,pub_bids)) b