問題描述

有兩組數據(兩張表)用于患者記錄，一組是 1999-2003，另一組是 2004-2009.每個都有 >100 列；Table_A 有 ~8 個獨特的列，Table_B ~ 25 個獨特的列(相互比較).我的目標是:

Have two sets of data (two tables) for patient records, one 1999-2003, the other 2004-2009. Each has >100 columns; Table_A has ~8 unique columns, Table_B ~ 25 unique columns (Compared to each other). My goal is:

1. 包含 1999-2009 年所有數據的單個表格
2. 對于一個表中不在另一個表中的行，只需將該列的值設為 NULL.例如如果表A有Diagnostic_Category_12而Table_B沒有，則該值將是表A中的原始值，而表B中為NULL

我見過一種手動執行此操作的方法:合并具有不同列數的兩個表

I've seen a method for doing this manually: Unioning Two Tables With Different Number Of Columns

但是，此數據集中的列太多，無法逐一輸入 - 我只想自動創建列并根據需要插入 NULL 值.

However there are far too many columns in this data set to type each one in - I'd like to just auto-create columns and insert NULL values as needed.

我使用的是 SQL Server 2008R2.

I am using SQL Server 2008R2.

推薦答案

更聰明地工作，而不是更努力.

Work smarter, not harder.

我建議您通過查詢您的架構來構建一些 SQL...這樣您就不會因為手工編寫而遺漏任何東西.您可以像這樣生成腳本(只需將 @tableName1 和 @tableName2 值替換為適當的表名):

I'd recommend that you build up some SQL by querying your schema... this way you don't miss anything by writing things by hand. You can generate the script like so (just replace @tableName1 and @tableName2 values with the appropriate table names):

declare
 @tableName1 sysname = 'myfirsttablename'
,@tableName2 sysname = 'mysecondtablename'
,@select varchar(max) = 'select';

declare @columns table
(
     Id int identity(1,1)
    ,ColumName nvarchar(128)
    ,ExistsInTable1 bit
    ,ExistsInTable2 bit
);

-- Get a column listing with flags for their existence in each table
insert @columns
select distinct
 quotename(c.Column_Name)
,iif(c2.Table_Name is null, 0, 1)
,iif(c3.Table_Name is null, 0, 1)
from Information_Schema.Columns as c
    left join Information_Schema.Columns as c2
    on c2.Column_Name = c.Column_Name
    and c2.Table_Name = @tableName1
    left join Information_Schema.Columns as c3
    on c3.Column_Name = c.Column_Name
    and c3.Table_Name = @tableName2 
where c.Table_Name in (@tableName1, @tableName2);

-- Build the select statement for the 1sttable (using null where the column is absent)
select
 @select += char(10) + iif(c.Id = 1, ' ', ',') 
+ iif(c.ExistsInTable1 = 1, c.ColumName, 'null') + ' as ' + c.ColumName
from @columns as c
order by c.Id;

set @select += '
from ' + quotename(@tableName1) + '
union all
select';

-- Build the select statement for the 2ndtable (using null where the column is absent)
select
 @select += char(10) + iif(c.Id = 1, ' ', ',') 
+ iif(c.ExistsInTable2 = 1, c.ColumName, 'null') + ' as ' + c.ColumName
from @columns as c
order by c.Id;

set @select += '
from ' + quotename(@tableName2);

-- Print or execute your sql.
print(@select); -- or exec(@select);

生成 SQL 后，我建議您:

Once you've generated your SQL, I'd recommend that you:

1. 驗證您的結果并根據需要調整您的查詢.
2. 將最終的 SQL 放在存儲過程中，而不是為每個請求即時生成它.

這篇關于SQL - 聯合兩個表，每個表都有幾個唯一的列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！