如何关联3张表实现同报价下多供应商价格横向展示

Hey there! It sounds like you're stuck trying to pivot your data so that each provider's price for a single quote shows up as a separate column instead of spread across multiple rows. Let's walk through the solutions for this—there are a couple of approaches depending on whether you know your provider list in advance or need something flexible.

静态列方案（已知供应商数量/名称） #

If you have a fixed set of providers (like the 3 you mentioned), you can use a combination of CASE WHEN and GROUP BY to pivot the rows into columns. This works across most SQL databases and is straightforward:

SELECT
    q.origin,
    q.destination,
    -- Map each provider to a dedicated column
    MAX(CASE WHEN p.provider_name = 'Provider Name 1' THEN pr.price END) AS "Provider Name 1",
    MAX(CASE WHEN p.provider_name = 'Provider Name 2' THEN pr.price END) AS "Provider Name 2",
    MAX(CASE WHEN p.provider_name = 'Provider Name 3' THEN pr.price END) AS "Provider Name 3"
FROM Quote q
INNER JOIN Price pr ON q.id_quote = pr.id_quote
INNER JOIN Provider p ON pr.id_provider = p.id_provider
-- Group by quote identifiers to collapse multiple rows into one per quote
GROUP BY q.id_quote, q.origin, q.destination
ORDER BY q.id_quote;

How this works: #

• The CASE WHEN checks which provider each row belongs to and pulls the corresponding price.
• MAX() (or MIN()—either works here) ensures that for each quote, we only keep the price for each provider (since GROUP BY collapses all rows for a single quote into one).
• If a provider doesn't have a price for a quote, the column will show NULL—you can use ISNULL(MAX(...), 0) to replace NULL with a default value like 0 if needed.

Alternatively, if your database supports the PIVOT operator (like SQL Server, PostgreSQL 11+, or Oracle), you can use that for cleaner syntax:

SELECT origin, destination, [Provider Name 1], [Provider Name 2], [Provider Name 3]
FROM (
    -- Subquery to get the base data we need to pivot
    SELECT q.origin, q.destination, p.provider_name, pr.price
    FROM Quote q
    JOIN Price pr ON q.id_quote = pr.id_quote
    JOIN Provider p ON pr.id_provider = p.id_provider
) AS SourceData
PIVOT (
    MAX(price) -- Aggregate the price value
    FOR provider_name IN ([Provider Name 1], [Provider Name 2], [Provider Name 3]) -- Define the columns to pivot into
) AS PivotedData;

动态列方案（供应商数量不固定） #

If your provider list might grow or change over time, a static solution won't cut it. You'll need to generate dynamic SQL that automatically adapts to the current list of providers. Here's an example for SQL Server:

DECLARE @columnList AS NVARCHAR(MAX),
        @dynamicQuery AS NVARCHAR(MAX);

-- First, generate a comma-separated list of provider names formatted as quoted columns
SELECT @columnList = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(p.provider_name)
    FROM Provider p
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Build the full pivot query using the generated column list
SET @dynamicQuery = '
SELECT origin, destination, ' + @columnList + '
FROM (
    SELECT q.origin, q.destination, p.provider_name, pr.price
    FROM Quote q
    JOIN Price pr ON q.id_quote = pr.id_quote
    JOIN Provider p ON pr.id_provider = p.id_provider
) AS SourceData
PIVOT (
    MAX(price)
    FOR provider_name IN (' + @columnList + ')
) AS PivotedData';

-- Execute the dynamic query
EXEC sp_executesql @dynamicQuery;

How this works: #

• The first part uses FOR XML PATH('') to concatenate all provider names into a formatted list of columns (e.g., [Provider A],[Provider B]).
• We then plug this list into a dynamic PIVOT query, which will automatically include every provider from the Provider table as a column.

额外注意点 #

• Make sure the (id_quote, id_provider) combination in the Price table is unique. If there are multiple prices for the same quote and provider, you'll need to adjust the aggregate function (e.g., use AVG() if you want an average, or SUM() if you want a total).
• If you need to handle quotes that have no prices from any provider, switch to LEFT JOIN instead of INNER JOIN to include those quotes in the results.

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