如何编写SQL查询实现两表关联并将列值转为列标题

Hey Ayyanar, let's work through this problem where we need to join the Item Master and Fabric Table, then pivot the size values from Item Master into column headers with corresponding qty from Fabric Table.

Step 1: Understand the Data Relationships #

• The common key linking both tables is barcode — we'll use this to establish our join.
• We need to group results by Lot No, Job No, design, and shade, then turn each unique size value into a column that displays the matching quantity from the Fabric Table.

Step 2: SQL Query Implementation #

Depending on your database system, here are two reliable approaches:

Option 1: CASE Statement Approach (Works in Most Databases like MySQL, PostgreSQL)

This method is universally compatible across most SQL databases:

SELECT
    ft.`Lot No`,
    ft.`Job No`,
    ft.design,
    ft.shade,
    SUM(CASE WHEN im.size = '36' THEN ft.qty ELSE 0 END) AS `36`,
    SUM(CASE WHEN im.size = '38' THEN ft.qty ELSE 0 END) AS `38`,
    SUM(CASE WHEN im.size = '40' THEN ft.qty ELSE 0 END) AS `40`,
    SUM(CASE WHEN im.size = '42' THEN ft.qty ELSE 0 END) AS `42`,
    SUM(CASE WHEN im.size = '44' THEN ft.qty ELSE 0 END) AS `44`,
    SUM(CASE WHEN im.size = '46' THEN ft.qty ELSE 0 END) AS `46`
FROM
    `Fabric Table` ft
INNER JOIN
    `Item master` im ON ft.barcode = im.barcode
GROUP BY
    ft.`Lot No`,
    ft.`Job No`,
    ft.design,
    ft.shade
ORDER BY
    ft.`Lot No`,
    ft.`Job No`,
    ft.shade;

Option 2: PIVOT Clause Approach (For SQL Server, Oracle, etc.)

If your database supports the PIVOT operator, this is a more concise solution:

SELECT
    `Lot No`,
    `Job No`,
    design,
    shade,
    [36], [38], [40], [42], [44], [46]
FROM (
    -- Subquery to get joined data with size and qty
    SELECT
        ft.`Lot No`,
        ft.`Job No`,
        ft.design,
        ft.shade,
        im.size,
        ft.qty
    FROM
        `Fabric Table` ft
    INNER JOIN
        `Item master` im ON ft.barcode = im.barcode
) AS SourceData
PIVOT (
    SUM(qty)
    FOR size IN ([36], [38], [40], [42], [44], [46])
) AS PivotedResults
ORDER BY
    `Lot No`,
    `Job No`,
    shade;

Step 3: Expected Output #

Both queries will generate exactly the format you requested:

Lot No          Job No          design  shade 36  38  40  42  44  46
LOT/001/17-18   JOB/001/17-18  87282   1     3   12  13  5   11  0
LOT/001/17-18   JOB/002/17-18  87282   2     30  34  30  13  2   11

(Note: The 0 in the first row's 46 column is expected since there's no matching size 46 record in your sample data.)

Important Notes #

• We use SUM() in both approaches to handle any potential duplicate barcodes (your sample has one-to-one mappings, but this makes the query robust for edge cases).
• Table names with spaces need to be wrapped in backticks (MySQL) or square brackets (SQL Server) — adjust the syntax to match your database's requirements.
• The ORDER BY clause ensures results are sorted to match your desired output order.

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