MYSQL版
这是查询。连接查询使用以下命令为每个客户组内的每个产品生成 RowNumber (1,2,3,...)用户定义变量 MySQL 功能 http://dev.mysql.com/doc/refman/5.0/en/user-variables.html。外部查询使用以下方式形成 PIVOT 表GROUP BY
以及带有内表行号的 CASE。如果您需要改变产品列数,请考虑创建此查询动态添加MAX(CASE WHEN p.RowNum=X THEN p.Product END) as ProductX
到选择列表。
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 http://sqlfiddle.com/#!2/c60f7/5
SQL Server 版本:
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 http://sqlfiddle.com/#!6/dc654/3