如何在没有聚合函数的情况下在 sql server 中创建数据透视查询 [英] How to create a pivot query in sql server without aggregate function

问题描述

我正在使用 MS SQL SERVER 2008 并且我有以下数据:

I am using MS SQL SERVER 2008 and I have following data:

select * from account;

| PERIOD | ACCOUNT | VALUE |
----------------------------
|   2000 |   Asset |   205 |
|   2000 |  Equity |   365 |
|   2000 |  Profit |   524 |
|   2001 |   Asset |   142 |
|   2001 |  Equity |   214 |
|   2001 |  Profit |   421 |
|   2002 |   Asset |   421 |
|   2002 |  Equity |   163 |
|   2002 |  Profit |   325 |

我想让它们变成这样:

| ACCOUNT | 2000 | 2001 | 2002 |
--------------------------------
|   Asset |  205 |  142 |  421 |
|  Equity |  365 |  214 |  163 |
|  Profit |  524 |  421 |  325 |

我尝试查询使用 pivot query 但该值必须使用 aggregate function 并且结果不合适.我该怎么办?

I've tried to query use pivot query but the value have to use aggregate function and the result is not appropriate. what should I do?

推荐答案

SELECT *
FROM
(
SELECT [Period], [Account], [Value]
FROM TableName
) AS source
PIVOT
(
    MAX([Value])
    FOR [Period] IN ([2000], [2001], [2002])
) as pvt

另一种方式，

SELECT ACCOUNT,
      MAX(CASE WHEN Period = '2000' THEN Value ELSE NULL END) [2000],
      MAX(CASE WHEN Period = '2001' THEN Value ELSE NULL END) [2001],
      MAX(CASE WHEN Period = '2002' THEN Value ELSE NULL END) [2002]
FROM tableName
GROUP BY Account

• SQLFiddle 演示(两个查询)

这篇关于如何在没有聚合函数的情况下在 sql server 中创建数据透视查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！