如何插入/組合來自多個表的值？

我有多個表（每個月 12 個），每個表有 3 列（“code”、“test_name”、“value”）

例子：

---

January
code | test_name | value
0001   name1       17
0002   name2       45
0005   name5       12

---

February
code | test_name | value
0001   name1       3
0002   name2       7
0004   name4       13

---

March
code | test_name | value
0001   name1       6
0006   name6       32
0007   name7       41

---

如何在 sql 中將其合併或插入或組合到一個如下所示的表中：

Year
code | test_name | January | February | March | ...
0001   name1       17        3          6
0002   name2       45        7          0
0003   name3       0         0          0
0004   name4       0         13         0
0005   name5       12        0          0
0006   name6       0         0          32
0007   name7       0         0          41

我為 SQL 2012 編寫了這個，但它應該在 2008R2 及更高版本中工作。

DECLARE @January AS TABLE
   (
     Code CHAR(4) NOT NULL
   , test_name VARCHAR(100) NOT NULL
   , value INT NOT NULL
   );

INSERT  INTO @January
       ( Code, test_name, value )
VALUES  ( '0001', 'name1', 17 )
   ,   ( '0002', 'name2', 45 )
   ,   ( '0005', 'name5', 12 );

DECLARE @February AS TABLE
   (
     Code CHAR(4) NOT NULL
   , test_name VARCHAR(100) NOT NULL
   , value INT NOT NULL
   );

INSERT  INTO @February
       ( Code, test_name, value )
VALUES  ( '0001', 'name1', 3 )
   ,   ( '0002', 'name2', 7 )
   ,   ( '0004', 'name4', 13 );

DECLARE @march AS TABLE
   (
     Code CHAR(4) NOT NULL
   , test_name VARCHAR(100) NOT NULL
   , value INT NOT NULL
   );

INSERT  INTO @march
       ( Code, test_name, value )
VALUES  ( '0001', 'name1', 6 )
   ,   ( '0006', 'name6', 32 )
   ,   ( '0007', 'name7', 41 );


WITH CTE_AllMonths AS
   (
   SELECT Code, test_name, value, 'January' AS SourceMonth
   FROM @January
   UNION ALL
   SELECT Code, test_name, value, 'February' AS SourceMonth
   FROM @February 
   UNION ALL
   SELECT Code, test_name, value, 'March' AS SourceMonth
   FROM @March  
   )
   SELECT Code, test_name, January, February, March
   FROM CTE_AllMonths 
       PIVOT (SUM(value) FOR SourceMonth IN ([January], [February], [March])) AS PT

CTE 更優雅，但您也可以使用 CASE

CREATE TABLE #January (code varchar(4), test_name varchar(5), value INT)
CREATE TABLE #February (code varchar(4), test_name varchar(5), value INT)
CREATE TABLE #March (code varchar(4), test_name varchar(5), value INT)

INSERT INTO #January VALUES ('0001','name1',17)
INSERT INTO #January VALUES ('0002','name2',45)
INSERT INTO #January VALUES ('0005','name5',12)

INSERT INTO #February VALUES ('0001','name1',3)
INSERT INTO #February VALUES ('0002','name2',7)
INSERT INTO #February VALUES ('0004','name4',13)

INSERT INTO #March VALUES ('0001','name1',6)
INSERT INTO #March VALUES ('0006','name6',32)
INSERT INTO #March VALUES ('0007','name7',41)

SELECT 
code, test_name
, SUM(ISNULL(CASE WHEN [Month] = 'January' THEN VALUE END,0)) AS 'January'
, SUM(ISNULL(CASE WHEN [Month] = 'February' THEN VALUE END,0)) AS 'February'
, SUM(ISNULL(CASE WHEN [Month] = 'March' THEN VALUE END,0)) AS 'March'
FROM (
SELECT 'January' AS 'Month', * FROM #January
UNION ALL
SELECT 'February' AS 'Month', * FROM #February
UNION ALL
SELECT 'March' AS 'Month', * FROM #March
) a
GROUP BY a.code, a.test_name
ORDER BY a.code, a.test_name


DROP TABLE #January
DROP TABLE #February
DROP TABLE #March

引用自：https://dba.stackexchange.com/questions/132310