SQL Server：如何合并多组type与name列值并转成行？是否需正则？

Great question! You don't need regular expressions to solve this—SQL Server has straightforward, built-in ways to unpivot these paired columns into the rows you want. Here's the simplest approach using CROSS APPLY with a VALUES clause, which lets you explicitly map each type-name column pair into a new row:

SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id,
    CONCAT(unpivoted.type_val, '-', unpivoted.name_val) AS results
FROM Data
CROSS APPLY (
    VALUES
        (col_1_type, col_1_name),
        (col_2_type, col_2_name),
        (col_3_type, col_3_name)
) AS unpivoted(type_val, name_val);

How this works: #

• CROSS APPLY (VALUES ...): This takes each row from your Data table and generates three new rows (one for each type-name pair). Each entry in the VALUES list corresponds to one of your column pairs.
• CONCAT(): Combines the type and name values into the "type-name" format you need.
• ROW_NUMBER(): Generates the sequential Id values shown in your expected output. If you wanted to retain the original Id and add a sub-number (like 1-1, 1-2, etc.), you could use CONCAT(Original.Id, '-', ROW_NUMBER() OVER (PARTITION BY Original.Id ORDER BY (SELECT NULL))) instead.

What if you have many column pairs? #

If you have a variable or large number of col_N_type/col_N_name columns, you could generate the query dynamically using system catalog views (like INFORMATION_SCHEMA.COLUMNS) to list the columns and build the VALUES clause automatically. Even then, regex isn't required—you can use string functions to extract the numeric suffix from column names and group them. But for your current scenario with three fixed pairs, the static query above is the most efficient and readable solution.

内容的提问来源于stack exchange，提问作者Rama Ramadhan