How to pivot? How to convert multiple rows into one row with multiple columns?
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