如何使用SQL转置表格？ [英] How transpose a table using SQL?

问题描述

我有一个这样的表：

id | P  | C | A |  B
--------------------
1  |100 |3  |a1 | b1
2  |101 |3  |a2 | b2
3  |102 |3  |a3 | b3
4  |103 |3  |a4 | b4
5  |100 |4  |a5 | b5 
6  |101 |4  |a6 | b6 
7  |102 |4  |a7 | b7
8  |103 |4  |a8 | b8

我想获得一个新的转置结构，如下所示：

I want to get a new transposed structure like this:

 P  |_3A | _3B |_4A | _4B   
-------------------------
100 | a1 |  b1 | a5 | b5
101 | a2 |  b2 | a6 | b6  
102 | a3 |  b3 | a7 | b7
103 | a4 |  b4 | a8 | b8

如您所见，新字段名已从原始表的C字段中提取。
是否可以使用SQL来做到这一点？

As you can see ,new field names have been extracted from C field in the original table. Is there any way to do this using SQL?

推荐答案

Postgres在数组和交叉表。但是，一种独立于数据库的方式是使用聚合：

Postgres has some advanced functionality in terms of arrays and crosstab. However, a database independent way of doing this is by using aggregation:

select t.p,
       max(case when c = 3 then a end) as a3,
       max(case when c = 3 then b end) as b3,
       max(case when c = 4 then a end) as a4,
       max(case when c = 4 then b end) as b4
from atable t
group by t.p;

这将在SQLite和Postgres（以及几乎所有其他数据库）中均可使用。

This will work in both SQLite and Postgres (and just about any other database).

这篇关于如何使用SQL转置表格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！