Refactor SQL query to return results into rows instead of columns

I have a SQL query that need to be refactored. Basically the query gets all the producttypes ordered by a specified customer. The problem is that the results are returned in columns instead of rows. This needs to be changed the other way around to make the query more generic.

So this is what the query returns:

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

And this is what it should be:

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

This is the query which I have simplified a bit:

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

So I have been thinking about splitting the joins into a separate stored proc and then call this as I need more productTypes but I can't seem to get this working. Anyone an idea on how to get this working ?

Doing things in columns is actually usually much more difficult.

Assuming normalized tables Customers, Products and Orders, you shouldn't need to do anything more than just:

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

If this doesn't work, please list structures of the involved tables.

I'm going to assume your tables looks something like this

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

Then your query is

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