如何转动？如何将多行转换为多列的一行？

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.

我有两个表，我想要结合起来。第一个表是客户端，另一个是产品。目前我有22个产品，但我希望有一个灵活的数据库设计，所以在产品数据库中没有22列，每个客户每个产品有1行，所以如果我添加或删除1个产品，我不会必须改变数据库结构。

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.

我想要一个select语句，我为每个客户选择所有产品，输出应该在一行中，每个产品都有一列。

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.

假设有2个客户和3个产品。

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.

完美的解决方案也是灵活的，因为select语句应该计算每个客户端的不同产品的数量（对于所有客户端它们将始终相同），这样如果我为所有客户添加或删除1个产品，我不应该更改select语句。

1 个解决方案

#1

MYSQL Edition

MYSQL版

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.

这是查询。联接查询使用用户定义变量MySQL功能为每个客户端组内的每个产品生成RowNumber（1,2,3，...）。外部查询使用GROUP BY和CASE与内部表中的行号形成PIVOT表。如果您需要变量产品列数，则考虑创建此查询动态添加MAX（CASE WHEN p.RowNum = X THEN p.Product END）作为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

SQLFiddle演示

SQL Server Edition:

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

SQLFiddle演示