sql - 行在自己的列中取决于它们的值

我有一个选择查询，目前产生以下结果:

Description   Code    Price
 Product 1     A        5
 Product 1     B        4
 Product 1     C        2

使用以下查询:

SELECT DISTINCT np.Description, p.promotionalCode, p.Price
FROM            Price AS p INNER JOIN
                         nProduct AS np ON p.nProduct = np.Id

我想生产以下内容:

Description  A  B  C
 Product 1   5  4  2

最佳答案

SELECT
    np.Id,
    np.Description,
    MIN(Case promotionalCode WHEN 'A' THEN Price ELSE NULL END) AS 'A',
    MIN(Case promotionalCode WHEN 'B' THEN Price ELSE NULL END) AS 'B',
    MIN(Case promotionalCode WHEN 'C' THEN Price ELSE NULL END) AS 'C'
FROM
    Price AS p
INNER JOIN nProduct AS np ON p.nProduct = np.Id
GROUP BY
    np.Id,
    np.Description

下面是一个简单的测试示例:

DECLARE @temp TABLE (
    id INT,
    description varchar(50),
    promotionalCode char(1),
    Price smallmoney
)

INSERT INTO @temp
select 1, 'Product 1', 'A', 5
    union
SELECT 1, 'Product 1',  'B', 4
    union
SELECT 1, 'Product 1', 'C', 2



SELECT
    id,
    description,
    MIN(Case promotionalCode WHEN 'A' THEN Price ELSE NULL END) AS 'A',
    MIN(Case promotionalCode WHEN 'B' THEN Price ELSE NULL END) AS 'B',
    MIN(Case promotionalCode WHEN 'C' THEN Price ELSE NULL END) AS 'C'
FROM
     @temp
GROUP BY
    id,
    description

关于sql - 行在自己的列中取决于它们的值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/236236/