SQL新手求助：将含唯一值的列转置为行实现方案

Hey there! Let's figure out how to turn those duplicate BARCODE rows into a single row with combined material details. Based on your sample data, it looks like each BARCODE has either 1 or 2 entries—here's a straightforward solution that works in most SQL databases:

Step-by-Step Solution #

First, we'll assign a row number to each entry under the same BARCODE (ordered by MATERIAL to keep things consistent). Then we'll join the first entry with the second one (if it exists) to get your desired single-row format.

SELECT 
    t1.BARCODE,
    t1.MATERIAL AS MATERIAL,
    t1.`SET` AS `SET`,
    t1.WEIGHT AS WEIGHT,
    t2.MATERIAL AS MATERIAL,
    t2.`SET` AS `SET`,
    t2.WEIGHT AS WEIGHT
FROM (
    -- Assign row numbers to each entry per BARCODE
    SELECT 
        BARCODE,
        MATERIAL,
        `SET`,
        WEIGHT,
        ROW_NUMBER() OVER(PARTITION BY BARCODE ORDER BY MATERIAL) AS row_num
    FROM your_table_name -- Replace with your actual table name
) t1
-- Left join to include entries that only have one row
LEFT JOIN (
    SELECT 
        BARCODE,
        MATERIAL,
        `SET`,
        WEIGHT,
        ROW_NUMBER() OVER(PARTITION BY BARCODE ORDER BY MATERIAL) AS row_num
    FROM your_table_name
) t2 
    ON t1.BARCODE = t2.BARCODE 
    AND t2.row_num = 2
WHERE t1.row_num = 1
ORDER BY t1.BARCODE;

Quick Notes: #

• Replace your_table_name with the actual name of your table.
• SET is a reserved keyword in SQL, so we wrap it in backticks (`) for MySQL/MariaDB. If you're using SQL Server, use square brackets ([SET]) instead.
• For BARCODEs with only one entry (like 112347), the second set of MATERIAL, SET, and WEIGHT columns will return NULL—which matches your desired output.

If you're working with a specific database (like PostgreSQL, Oracle, etc.), feel free to mention it and I can tweak the solution to use database-specific functions!

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