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