MySQL多列数据合并为单列输出的实现方法咨询

Absolutely! This is a classic "unpivot" scenario, and both CONCAT and UNION (or their tool-specific variants) can come into play here—though they serve different purposes depending on what tool you're using. Let's break this down step by step for common tools:

In SQL, UNION ALL (stick with ALL unless you need to remove duplicate product-name pairs) is exactly what you need for this task. CONCAT on the other hand, is for combining text values within the same row (like merging a first and last name), so it won't help you turn columns into rows directly.

Using UNION ALL (works in all SQL dialects) #

Suppose your table is named product_data, with unique_name as column A, and product_1, product_2, product_3 as columns B, C, D:

SELECT unique_name, product_1 AS product
FROM product_data
WHERE product_1 IS NOT NULL -- Skip empty cells
UNION ALL
SELECT unique_name, product_2 AS product
FROM product_data
WHERE product_2 IS NOT NULL
UNION ALL
SELECT unique_name, product_3 AS product
FROM product_data
WHERE product_3 IS NOT NULL
-- Repeat for every additional product column

Using UNPIVOT (for SQL Server, Oracle, etc.) #

If your SQL dialect supports the UNPIVOT operator, you can do this in a cleaner, more concise way:

SELECT unique_name, product
FROM product_data
UNPIVOT (
  product FOR product_columns IN (product_1, product_2, product_3)
) AS unpivoted_results

In Excel, CONCAT isn't the right fit here—instead, you'll use Power Query (the easiest, most scalable method) or dynamic array formulas with UNION (available in Excel 365/2021).

Method 1: Power Query (Recommended) #

• Select your entire data range, go to the Data tab → From Table/Range (make sure your data has headers).
• In the Power Query Editor, select your unique_name column (column A), then go to the Transform tab → Unpivot Columns → Unpivot Other Columns.
• Rename the generated "Attribute" column to something like "Product" (your unified header), then click Close & Load to export the unpivoted table back to Excel.

Method 2: Dynamic Array Formula (Excel 365/2021) #

If you prefer formulas, use this dynamic array solution to stack your product columns and pair them with the corresponding names:

=LET(
    names, A2:A10,
    products, B2:D10,
    repeated_names, INDEX(names, ROUNDUP(SEQUENCE(ROWS(names)*COLUMNS(products))/COLUMNS(products), 0)),
    product_list, TOCOL(products, 1), -- 1 excludes empty cells
    HSTACK(repeated_names, product_list)
)

The UNION function could be used here to merge individual product columns, but the LET approach above is more straightforward for this scenario.

In Pandas, this is called "melting" a dataframe. pd.concat can be used to manually stack converted columns, but pd.melt is the dedicated function for this unpivot task.

Using pd.melt (Clean & Efficient) #

import pandas as pd

# Assume your dataframe is named df, with 'unique_name' as column A
unpivoted_df = pd.melt(
    df,
    id_vars=['unique_name'],  # Keep this column as-is
    value_name='product',     # Your unified product header
    ignore_index=True
)

# Drop rows with empty product values
unpivoted_df = unpivoted_df.dropna(subset=['product'])

Using pd.concat (Manual Approach) #

If you want to use concat explicitly, you can convert each product column to a separate dataframe and stack them:

import pandas as pd

dfs_to_concat = []
# Loop through all product columns (skip column A)
for col in df.columns[1:]:
    temp_df = df[['unique_name', col]].rename(columns={col: 'product'})
    dfs_to_concat.append(temp_df.dropna())

unpivoted_df = pd.concat(dfs_to_concat, ignore_index=True)

• CONCAT (or equivalents like CONCATENATE in Excel, || in SQL): Used to combine text within the same row. Not useful for your column-to-row unpivoting need.
• UNION (SQL) / UNION function (Excel) / pd.concat (Python): Used to combine rows from different datasets. This is exactly what you need—each product column becomes a set of rows, which you then merge into a single table with a unified header.

内容的提问来源于stack exchange，提问作者37A18