SQL -将记录转为列

Question

我有一个这样的表:间隔类01-02 class102-03 class203-04 class104 - 05 class2- 06 class1

我想要这样的结果

class1 class2
01-02  02-03
03-04  04-05
05-06  NULL

第一行是列名

谢谢你!

Answer 1

您可以按类枚举行，然后使用条件聚合:

select max(case when class = 'class1' then interval end) as class1,
max(case when class = 'class2' then interval end) as class2
from (select t.*,
row_number() over (partition by class order by interval) as seqnum
from t
) t
group by seqnum;

Answer 2

如果列数固定，则可以使用SELECT和GROUP BY。让我给你看一个表"T_Capital_Rainfall_Q"的例子(见下文):

SELECT  Capital, Cal_Year
, Max(Iif(Quart="Q1", Quart_Rainfall, Null)) AS Q1
, Max(Iif(Quart="Q2", Quart_Rainfall, Null)) AS Q2
, Max(Iif(Quart="Q3", Quart_Rainfall, Null)) AS Q3
, Max(Iif(Quart="Q4", Quart_Rainfall, Null)) AS Q4
FROM T_Capital_Rainfall_Q
GROUP BY Capital, Cal_Year ;

上面的SQL操作产生以下输出记录列表(没有对齐，抱歉):

Capital Cal_Year    Q1  Q2  Q3  Q4
Beijing 2018    0   4   7,8 17
Washington  2018    12.13   5.67    2.26    12.7

如果是可变列数，唯一的方法是使用TRANSFORM Query。

上面例子的表是:

T_Capital_Rainfall_Q
Capital Cal_Year    Quart   Quart_Rainfall
Beijing 2018    Q1  0
Beijing 2018    Q2  4
Beijing 2018    Q3  7.8
Beijing 2018    Q4  17
Washington  2018    Q1  12.13
Washington  2018    Q2  5.67
Washington  2018    Q3  2.26
Washington  2018    Q4  12.7

更多信息见LightningGuide.net。