重构 SQL 查询以将结果返回到行而不是列中

Question

我有一个需要重构的 SQL 查询。基本上，查询获取指定客户订购的所有产品类型。问题在于结果以列而不是行的形式返回。这需要以相反的方式进行更改以使查询更加通用。

这就是查询返回的内容：

Name   ProductType1   ProductType2   ProductType3
--------------------------------------------------
Marc   PT09           P15            PT33

这就是它应该的样子：

Name ProductType
----------------
Marc PT09
Marc P15
Marc PT33

这是我简化了一点的查询：

SELECT 
      CustomerData.Name as Name
      Product1.productType as ProductType1,
      Product2.productType as ProductType2,
      Product3.productType as ProductType3
FROM
    (SELECT ProductID, Name
            FROM 
                Customer 
                Orders
      WHERE Customer.ID = 111
    ) as CustomerData

LEFT JOIN (SELECT DISTINCT CP.ProductID as ProductID,
                           PC.Type as ProductType
           FROM 
                CustomerProduct CP,
                ProductCategory PC
           WHERE
                PC.Category = 'A'
                AND CP.ProductCategoryID = PC.ID
           )  as Product1
           on CustomerData.ProductID = Product1.ProductID

LEFT JOIN (SELECT DISTINCT CP.ProductID as ProductID,
                           PC.Type as ProductType
           FROM 
                CustomerProduct CP,
                ProductCategory PC
           WHERE
                PC.Category = 'B'
                AND CP.ProductCategoryID = PC.ID
           )  as Product2
           on CustomerData.ProductID = Product1.ProductID

LEFT JOIN (SELECT DISTINCT CP.ProductID as ProductID,
                           PC.Type as ProductType
           FROM 
                CustomerProduct CP,
                ProductCategory PC
           WHERE
                PC.Category = 'C'
                AND CP.ProductCategoryID = PC.ID
           )  as Product3
           on CustomerData.ProductID = Product1.ProductID

所以我一直在考虑将连接分割成一个单独的存储过程，然后调用它，因为我需要更多的产品类型，但我似乎无法让它工作。有人知道如何让它发挥作用吗？

Answer 1

在列中做事实际上通常要困难得多。

假设规范化表“客户”、“产品”和“订单”，您不需要做任何其他事情，而只是：

SELECT C.customer_name
     , P.product_type
FROM Customers C
  JOIN Orders O
    ON O.customer_id=C.customer_id
  JOIN Products P
    ON O.product_id=P.product_id
WHERE C.ID = 111

如果这不起作用，请列出相关表的结构。

Answer 2

我假设您的表看起来像这样

Customer

id | name
11 | Marc

Products

id | type
21 | PT09
22 | P15
23 | PT33

Orders

id | id_customer | id_product | quantity
31 | 11          | 21         | 4
32 | 11          | 22         | 6
33 | 11          | 23         | 8

那么您的查询是

SELECT 
    a.name,
    c.type
FROM 
    Customer a
LEFT JOIN 
    Orders b ON b.id_customer = a.id
LEFT JOIN 
    Products c ON c.id = b.id_product