时间戳

首页 > 解决方案 > 如何将行值设置为标题列

sql - 如何将行值设置为标题列

问题描述

编辑：我有一张账单收据表，如下所示。这里的fee_title 计数是未知的。可以有多个标题超过 3 个标题

|  rec_no|   fee_title  | amount|
---------------------------------
|     1  |   monthly fee|    200|
|     1  |     tie fee  |    100|
|     2  |  computer fee|    150|
|     3  |   monthly fee|    200|
---------------------------------

我想在收据上方显示为

|  rec_no|   monthly fee| tie fee| computer fee |
-------------------------------------------------
|     1  |     200      |    100|         0     |
|     2  |       0      |      0|        150    |
|     3  |     200      |    0  |          0    |
-------------------------------------------------

我发现要解决这个问题我必须使用 PIVOT 但我不知道如何使用它。任何人都可以帮助我

标签： sqlsql-serversql-server-2008pivot

解决方案

使用条件聚合更容易实现这一点：

SELECT rec_no,
       SUM(CASE WHEN fee_title = 'monthly fee' THEN amount ELSE 0 END) AS [monthly fee],
       SUM(CASE WHEN fee_title = 'tie fee' THEN amount ELSE 0 END) AS [tie fee],
       SUM(CASE WHEN fee_title = 'computer fee' THEN amount ELSE 0 END) AS [computer fee]
FROM receipts
GROUP BY rec_no

但你也可以写成PIVOT：

SELECT rec_no,
       COALESCE([monthly fee], 0) AS [monthly fee],
       COALESCE([tie fee], 0) AS [tie fee],
       COALESCE([computer fee], 0) AS [computer fee]
FROM receipts
PIVOT (
  SUM(amount)
  FOR fee_title IN ([monthly fee], [tie fee], [computer fee])
) AS p

在这两种情况下，输出都是：

rec_no  monthly fee     tie fee     computer fee
1       200             100         0
2       0               0           150
3       200             0           0

SQLFiddle 上的演示

推荐阅读