gpt4 book ai didi

sql - 如何使用数据透视表进行分组

转载 作者:行者123 更新时间:2023-12-04 02:45:07 25 4
gpt4 key购买 nike

有一个这样的表:,

name  ZK04_COUNT  ZK05_COUNT  ZK04_PRICE  ZK05_PRICE
A 15 0 150.00 0
A 0 20 0 223.00
B 11 0 75.00 0
B 0 24 0 250.00

我正试图将它转向这样的东西:

name  ZK04_COUNT  ZK05_COUNT  ZK04_PRICE  ZK05_PRICE
A 15 20 150.00 223.00
B 11 24 75.00 250.00

我的代码是这样的:

SELECT *
FROM (
SELECT
r.name
,r.AUART
, CASE WHEN r.AUART = 'ZK05' THEN count(r.MATNR) ELSE '' END AS ZK05_COUNT
, CASE WHEN r.AUART = 'ZK04' THEN count(r.MATNR) ELSE '' END AS ZK04_COUNT
, CONVERT(money, sum(CAST(netpr as MONEY)), 1) as ToplamTutar
FROM GARANTI_YP_RAPORU as r
group by r.name ,r.AUART
) as gTablo
PIVOT
(
SUM(ToplamTutar)

FOR gTablo.AUART IN ([ZK05],[ZK04])
)
AS p1

我希望单行输出。怎么做到的?

最佳答案

这真的不需要 PIVOT。
仅按名称对它们进行分组,然后使用条件聚合应该可以。

SELECT r.name
, COUNT(CASE WHEN r.AUART = 'ZK04' THEN r.MATNR END) AS ZK04_COUNT
, COUNT(CASE WHEN r.AUART = 'ZK05' THEN r.MATNR END) AS ZK05_COUNT
, CAST(SUM(CASE WHEN r.AUART = 'ZK04' THEN r.netpr ELSE 0 END) AS MONEY) AS ZK04_PRICE
, CAST(SUM(CASE WHEN r.AUART = 'ZK05' THEN r.netpr ELSE 0 END) AS MONEY) AS ZK05_PRICE
FROM GARANTI_YP_RAPORU as r
GROUP BY r.name
ORDER BY r.name

关于sql - 如何使用数据透视表进行分组,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57520660/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com