SQL Server中将多行多列合并为单行多列的实现方法

Got it, let's solve this problem where you need to turn 40 rows (each with 15 columns) into a single row with 600 columns in SQL Server. Since we're dealing with repeated column sets across rows, dynamic SQL is the way to go—it'll handle the tedious work of generating all those columns for you.

Key Notes First #

SQL Server doesn't allow duplicate column names in a result set, so we'll add a row number suffix to each column (e.g., sn_1, Name_1 for the first row, sn_2, Name_2 for the second, etc.) to avoid errors. This still gives you all the values in the single row format you need.

Solution Code (SQL Server 2017+) #

Assuming your table is named YourTable and you want to order rows by the sn column (adjust the order if needed):

DECLARE @sql NVARCHAR(MAX);
DECLARE @columns NVARCHAR(MAX);

-- Generate the dynamic column list: each row's columns become unique-named columns in the final row
SELECT @columns = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN sn = ', sn, ' THEN sn END) AS sn_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN Name END) AS Name_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN city END) AS city_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN cntry END) AS cntry_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN conti END) AS conti_', sn
        -- Add the remaining 10 columns here following the same pattern
    ), ','
)
FROM YourTable
ORDER BY sn;

-- Build and execute the final query
SET @sql = 'SELECT ' + @columns + ' FROM YourTable';
EXEC sp_executesql @sql;

For SQL Server Versions Before 2017 #

If you're using an older version that doesn't support STRING_AGG, replace the column generation part with this FOR XML PATH method:

DECLARE @sql NVARCHAR(MAX);
DECLARE @columns NVARCHAR(MAX);

SELECT @columns = STUFF((
    SELECT ',' + CONCAT(
        'MAX(CASE WHEN sn = ', sn, ' THEN sn END) AS sn_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN Name END) AS Name_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN city END) AS city_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN cntry END) AS cntry_', sn, ',',
        'MAX(CASE WHEN sn = ', sn, ' THEN conti END) AS conti_', sn
        -- Add remaining columns here
    )
    FROM YourTable
    ORDER BY sn
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @sql = 'SELECT ' + @columns + ' FROM YourTable';
EXEC sp_executesql @sql;

If Your sn Column Isn't Continuous #

If your sn values aren't sequential (or you want to order by a different column), use ROW_NUMBER() to generate a consistent row order:

DECLARE @sql NVARCHAR(MAX);
DECLARE @columns NVARCHAR(MAX);

WITH NumberedRows AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY sn) AS RowNum -- Replace ORDER BY with your preferred sort column
    FROM YourTable
)
SELECT @columns = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN sn END) AS sn_', RowNum, ',',
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN Name END) AS Name_', RowNum, ',',
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN city END) AS city_', RowNum, ',',
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN cntry END) AS cntry_', RowNum, ',',
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN conti END) AS conti_', RowNum
        -- Add remaining columns here
    ), ','
)
FROM NumberedRows;

SET @sql = 'SELECT ' + @columns + ' FROM NumberedRows';
EXEC sp_executesql @sql;

How It Works #

• We use MAX(CASE...) to pick out values from each individual row and map them to a new column. Since each CASE only returns a value for one row (and NULL for others), MAX effectively grabs the non-NULL value for each column.
• Dynamic SQL builds the full list of columns automatically, so you don't have to write 600 lines of code manually.

内容的提问来源于stack exchange，提问作者Shahin P