如何枢纽?如何将多行转换为多列的一行? [英] How to pivot? How to convert multiple rows into one row with multiple columns?

查看:105
本文介绍了如何枢纽?如何将多行转换为多列的一行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个表要合并.第一个表与客户一起使用,另一个表与产品一起使用.目前,我有22种产品,但我希望有一个灵活的数据库设计,因此与其在产品数据库中没有22列,我为每个客户为每个产品提供1行,所以如果我总体上添加或删除1种产品,我不会必须更改数据库结构.

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.

我希望有一条select语句,在这里我为每个客户端选择所有产品,并且输出应该在一行中,每个产品都有一列.

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.

我看到了其他一些类似的问题,但是这样做的目的是将所有行连接到1列中,这是我不想要的.

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.

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

Assuming 2 clients and 3 products.

表客户端:

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

餐桌产品

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

输出应类似于:

表输出:

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

从选择语句应该计算每个客户的独特产品数量(对于所有客户而言,它们始终相同)的角度来看,完美的解决方案也将是灵活的,例如,如果我为所有客户添加或删除1个产品客户,我不应该更改select语句.

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.

推荐答案

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.

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演示

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演示

这篇关于如何枢纽?如何将多行转换为多列的一行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆