How to pivot? How to convert multiple rows into one row with multiple columns?

Question

I have two tables which I want to combine. The first table is with clients and the other with products. Currently I have 22 products, but I want to have a flexible DB design so instead of having 22 columns in the product DB, I have 1 row for each product for each client so if I add or remove 1 product overall, I don't have to change the DB structure.

I want to have a select statement where I select all products for each client and the output should be in a single row with a column for each product.

I have seen some other questions which are similar, but there the aim is to have all the rows concatenated in 1 column- which I don't want.

Assuming 2 clients and 3 products.

Table client:

ClientId | ClientName
---------------------
 1       | Name1
 2       | Name2

Table products

ProductId | ClientId | Product
-------------------------------------
 1        |   1      |  SomeproductA
 2        |   1      |  SomeproductB
 3        |   1      |  SomeproductA
 4        |   2      |  SomeproductC
 5        |   2      |  SomeproductD
 6        |   2      |  SomeproductA

The output should be something like:

Table output:

 ClientId | ClientName | Product1     | Product 2    | Product 3
 -------------------------------------------------------------------
     1    | Name1      | SomeproductA | SomeproductB | SomeproductA
     2    | Name2      | SomeproductC | SomeproductD | SomeproductA

The perfect solution would also be flexible in the sense that the select statement should count the number of distinct products for each client (they will always be the same for all clients), such that if I add or remove 1 product for all clients, I should not change the select statement.

Answer 1

MYSQL Edition

Here is the query. The joined query generates RowNumber (1,2,3,...) for each product inside each client group using User Defined Variables MySQL feature. The outer query forms a PIVOT table using GROUP BY and CASE with Row Numbers from the inner table. If you need to variable products column count then consider creating this query dynamic adding MAX(CASE WHEN p.RowNum=X THEN p.Product END) as ProductX to the select list.

select Clients.ClientName,
       MAX(CASE WHEN p.RowNum=1 THEN p.Product END) as Product1,
       MAX(CASE WHEN p.RowNum=2 THEN p.Product END) as Product2,
       MAX(CASE WHEN p.RowNum=3 THEN p.Product END) as Product3,
       MAX(CASE WHEN p.RowNum=4 THEN p.Product END) as Product4


FROM Clients
JOIN
(
  SELECT Products.*,
       if(@ClientId<>ClientId,@rn:=0,@rn),
       @ClientId:=ClientId,
       @rn:=@rn+1 as RowNum

  FROM Products, (Select @rn:=0,@ClientId:=0) as t
  ORDER BY ClientId,ProductID
 ) as P 
   ON Clients.ClientId=p.ClientId

GROUP BY Clients.ClientId

SQLFiddle demo

SQL Server Edition:

select Clients.ClientId,
       MAX(Clients.ClientName),
       MAX(CASE WHEN p.RowNum=1 THEN p.Product END) as Product1,
       MAX(CASE WHEN p.RowNum=2 THEN p.Product END) as Product2,
       MAX(CASE WHEN p.RowNum=3 THEN p.Product END) as Product3,
       MAX(CASE WHEN p.RowNum=4 THEN p.Product END) as Product4


FROM Clients
JOIN
(
  SELECT Products.*,
       ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY ProductID) 
         as RowNum

  FROM Products
 ) as P 
   ON Clients.ClientId=p.ClientId
GROUP BY Clients.ClientId

SQLFiddle demo

Answer 2

The answers seem to address both MySQL and SQL Server, so I am adding a further SQL Server Answer here and the logic might work in MySQL too.

Below is a dynamic SQL version in Transact SQL for MS SQL Server.

This enables you to get the same result without having to explicitly write out every column you need in the resultant table as the CASE WHEN solution. The CASE WHEN is nice and simple for a few columns, but I recently had a similar scenario that pivoted to around 200 columns.

For dynamic SQL you essentially compile the query that you want as a string using generated variables and then execute it.

-- variable tables to store data
DECLARE @Clients TABLE(ClientID int, 
                ClientName nvarchar(10))

DECLARE @Products TABLE(ProductID int, 
                    ClientID int, 
                    Product nvarchar(15))

-- populate the variable tables with sample data
INSERT INTO @Clients 
VALUES (1, 'Name1'),
    (2, 'Name2')

INSERT INTO @Products 
VALUES (1, 1, 'SomeproductA'),
    (2, 1, 'SomeproductB'),
    (3, 1, 'SomeproductA'),
    (4, 2, 'SomeproductC'),
    (5, 2, 'SomeproductD'),
    (6, 2, 'SomeproductA')

-- display the tables to check
SELECT * FROM @Clients
SELECT * FROM @Products

-- join the two tables and generate a column with rows which will become the new 
-- column names (Product_col) which gives a number to each product per client
SELECT c.ClientID, 
    c.ClientName, 
    p.ProductID, 
    p.Product,
    CONCAT('Product', ROW_NUMBER() 
        OVER(PARTITION BY c.ClientID ORDER BY p.Product ASC))  AS Product_col
INTO #Client_Products
FROM @Products p 
LEFT JOIN @Clients c ON c.ClientID = p.ClientID

-- view the joined data and future column headings
SELECT * FROM #Client_Products

-- setup for the pivot, declare the variables to contain the column names for pivoted 
-- rows and the query string
DECLARE @cols1 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

-- column name list for products
SET @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(Product_col) 
        FROM #Client_Products
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SELECT @cols1  -- view the future column names

-- generate query variable string

-- the top select is all the columns you want to actually see as the result
-- The inner query needs the columns you want to see in the result, and the columns 
-- you are pivoting with. The pivot needs to select the value you want to go into the 
-- new columns (MAX()) and the values that will become the column names (FOR x IN())
SET @query = 'SELECT ClientID, 
            ClientName,'
                + @cols1 +' 
            FROM
            (
                SELECT ClientID,
                    ClientName,
                    Product_col,
                    Product
                FROM #Client_Products
           ) x
         PIVOT 
        (
            MAX(Product)
            FOR Product_col IN (' + @cols1 + ')
        ) p'


EXECUTE(@query) -- execute the dynamic sql

DROP TABLE #Client_Products