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
請教各位大神
uj5u.com熱心網友回復：
union alluj5u.com熱心網友回復：
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 行受影響)
uj5u.com熱心網友回復：
union all 之后只有一列啊，要求把查詢結果作為兩列輸出。uj5u.com熱心網友回復：
;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
uj5u.com熱心網友回復：
cross applyuj5u.com熱心網友回復：
只有一個表怎么辦
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/123614.html
標籤：應用實例