时间戳

首页 > 解决方案 > 如何将 SQL PIVOT 用于多个聚合？

sql - 如何将 SQL PIVOT 用于多个聚合？

问题描述

我有一个如下所示的源表：

╔════════════════════╦════════════════╦══════════════╦══════════════════╗
║       Topic        ║     Person     ║ PersonsReply ║  PersonsComment  ║
╠════════════════════╬════════════════╬══════════════╬══════════════════╣
║ Is the earth flat? ║ This Person    ║ Yes          ║ It's flat.       ║
║ Is the earth flat? ║ That Person    ║ No           ║ It's round       ║
║ Is the earth flat? ║ Another Person ║ Maybe        ║ Don't care.      ║
╚════════════════════╩════════════════╩══════════════╩══════════════════╝

但是从我在网上看到的示例来看，我似乎无法将我的数据转换为如下表格表示：

╔════════════════════╦══════════════════╦══════════════════╦═════════════════════╦════════════════════╦════════════════════╦═══════════════════════╗
║       Topic        ║ ThisPersonsReply ║ ThatPersonsReply ║ AnotherPersonsReply ║ ThisPersonsComment ║ ThatPersonsComment ║ AnotherPersonsComment ║
╠════════════════════╬══════════════════╬══════════════════╬═════════════════════╬════════════════════╬════════════════════╬═══════════════════════╣
║ Is the earth flat? ║ Yes              ║ No               ║ Maybe               ║ It's flat          ║ It's round         ║ Don't care            ║
╚════════════════════╩══════════════════╩══════════════════╩═════════════════════╩════════════════════╩════════════════════╩═══════════════════════╝

如何使用 SQL 的 PIVOT 函数来实现我想要实现的目标？这是我的沙箱：http ://sqlfiddle.com/#!18/e198d/1

现在我得到：

topic   ThisReply   ThatReply   AnotherReply
Is the earth flat?  (null)  (null)  (null)

标签： sqlsql-servertsqlpivot

解决方案

你想要条件聚合：

select topic,
       max(case when Person = 'This' then reply end) as ThisPersonsReply,
       max(case when Person = 'That' then reply  end) as ThatPersonsReply,
       max(case when Person = 'Another' then reply  end) as AnotherPersonsReply,
       max(case when Person = 'This' then comment end) as ThisPersonsComment,
       max(case when Person = 'That' then comment end) as ThatPersonsComment,
       max(case when Person = 'Another' then comment end) as AnotherPersonsComment
from ptest pt
group by topic;

这是数据库小提琴。

推荐阅读