时间戳

首页 > 解决方案 > SQL - 如何将记录的行变成列？

sql - SQL - 如何将记录的行变成列？

问题描述

我需要一些帮助！！

我的平台中有一个“用户”表。在这张表中，我有如下信息：

id = is the user ID
created_at = is the date that the user created an agreement within the platform
agent = is responsible for serving the user

此信息采用以下格式：

id | created_at | deal_id | agent (columns of the table)
 1 | 2020-08-01 |       1 | 123456
 1 | 2020-09-01 |       2 | 123456
 1 | 2020-09-10 |       3 | 345676
 1 | 2020-10-29 |       4 | 456677

我想把这些数据如下：

 id | created_at1 | created_at2 | created_at3 | created_at4 | agent1 | agent2 | agent3 | agent4
  1 | 2020-08-01  | 2020-09-01  | 2020-09-10  | 2020-10-29  | 123456 | 123456 | 345676 | 456677

可能吗？我试图用最小值和最大值来做，但它只会给我两种情况。记住我给出了一个用户的例子，我希望它返回所有的 ID。

标签： sqlpivot

解决方案

您可以按如下方式使用条件聚合：

Select t.id,
       Max(case when deal_id = 1 then created_at end) as created_at1,
       Max(case when deal_id = 2 then created_at end) as created_at2,
       Max(case when deal_id = 3 then created_at end) as created_at3,
       Max(case when deal_id = 4 then created_at end) as created_at4,
       Max(case when deal_id = 1 then agent end) as agent1,
       Max(case when deal_id = 2 then agent end) as agent2,
       Max(case when deal_id = 3 then agent end) as agent3,
       Max(case when deal_id = 4 then agent end) as agent4
  From your_table t
 Group by id

推荐阅读