如何在没有聚合函数的情况下在 sql server 中创建数据透视查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14618316/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create a pivot query in sql server without aggregate function
提问by Fitrie Adytia Wibawa
I am using MS SQL SERVER 2008 and I have following data:
我正在使用 MS SQL SERVER 2008 并且我有以下数据：
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 |
I want to make them to be this:
我想让它们变成这样：
| ACCOUNT | 2000 | 2001 | 2002 |
--------------------------------
| Asset | 205 | 142 | 421 |
| Equity | 365 | 214 | 163 |
| Profit | 524 | 421 | 325 |
I've tried to query use pivot querybut the value have to use aggregate functionand the result is not appropriate. what should I do?
我试过查询使用，pivot query但值必须使用aggregate function，结果不合适。我该怎么办？
回答by John Woo
SELECT *
FROM
(
SELECT [Period], [Account], [Value]
FROM TableName
) AS source
PIVOT
(
MAX([Value])
FOR [Period] IN ([2000], [2001], [2002])
) as pvt
Another way,
其它的办法，
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
回答by bonCodigo
Check this out as well: using xml pathand pivot
也检查一下：使用xml path和pivot
| ACCOUNT | 2000 | 2001 | 2002 |
--------------------------------
| Asset | 205 | 142 | 421 |
| Equity | 365 | 214 | 163 |
| Profit | 524 | 421 | 325 |
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.period)
FROM demo c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT account, ' + @cols + ' from
(
select account
, value
, period
from demo
) x
pivot
(
max(value)
for period in (' + @cols + ')
) p '
execute(@query)
