SQL Server中如何将两个各含4列4行的表合并为8列8行的表？

Got it, let's tackle this problem step by step. From what you described, you have two tables in SQL Server, each with 4 columns and 4 rows, and you want to combine them into a single table with 8 columns and 8 rows.

Understanding the Requirement #

We need to retain all rows from both tables while expanding the result set to include all 8 columns (4 from each table). For rows originating from the first table, the columns belonging to the second table will be populated with NULL, and the reverse applies to rows from the second table.

Solution Using UNION ALL #

Assume your two tables are named Table1 (columns: Col1, Col2, Col3, Col4) and Table2 (columns: Col5, Col6, Col7, Col8). Here's the SQL query to achieve your goal:

-- Retrieve all rows from Table1, with Table2 columns set to NULL
SELECT 
    Col1, Col2, Col3, Col4,
    NULL AS Col5, NULL AS Col6, NULL AS Col7, NULL AS Col8
FROM Table1

UNION ALL

-- Retrieve all rows from Table2, with Table1 columns set to NULL
SELECT 
    NULL AS Col1, NULL AS Col2, NULL AS Col3, NULL AS Col4,
    Col5, Col6, Col7, Col8
FROM Table2

How This Works #

• UNION ALL combines the results of the two SELECT statements without removing duplicates (which we want here, since we need all 8 rows total).
• The first query returns the 4 rows from Table1 with NULL values for the columns from Table2.
• The second query returns the 4 rows from Table2 with NULL values for the columns from Table1.

The end result is a single result set with 8 columns and 8 rows, exactly as you requested.

If you actually intended to match rows from the two tables (e.g., pairing the first row of Table1 with the first row of Table2 to get 4 rows of 8 columns), feel free to clarify and I can adjust the solution accordingly.

内容的提问来源于stack exchange，提问作者Neeraj Bhanot