Oracle SQL rows to column with group by and sum

Question

我有一个包含以下数据的表。

ID    Name     DiscountGroup Discount DicountCategory
A     XXX      G025          25        MANGMNT
A     XXX      T005           5        HIGH GPA
A     XXX      T010          10        
B     XXXB     G040          40        SOMETXT
B     XXXB     T005           5        HIGH GPA

我想把数据显示为

ID    Name     PermanentDiscount Cateogry TempDiscount 
A     XXX      25                MANGMNT  15
B     XXXB     40                SOMETXT   5

。e永久折扣和临时折扣排成一行。所有永久折扣以GXXX开头，后跟3位数字。同样，所有临时折扣都以TXXX开头，后面跟着3位数字，其中XXX指的是实际折扣。一个ID可以有多个临时折扣，但只能有一个永久折扣。我试着用

SELECT *
FROM tableA
PIVOT (
MAX(Discount) FOR DiscountGroup IN ('')
);

但是我不能算出in list的值

Answer 1

可以为每个ID/Name添加一个包含临时折扣之和的列，然后过滤包含永久折扣的行。

select ID, Name, PermanentDiscount, Cateogry, TempDiscount
from (
select
DiscountGroup, ID, Name,
Discount as PermanentDiscount,
DicountCategory as Cateogry,
sum(case when DiscountGroup like 'T%' then Discount else 0 end) over(partition by ID, Name) as TempDiscount
from table
)
where DiscountGroup like 'G%'
order by ID, Name;

Answer 2

应该可以:

WITH sample_data AS 
( SELECT 'A' AS id, 'XXX' AS name, 'G025' AS DiscountGroup, 25 AS Discount, 'MANGMNT' AS DiscountCategory FROM DUAL UNION ALL
SELECT 'A' AS id, 'XXX' AS name, 'T005' AS DiscountGroup, 5 AS Discount, 'HIGH GPA' AS DiscountCategory FROM DUAL UNION ALL
SELECT 'A' AS id, 'XXX' AS name, 'T010' AS DiscountGroup, 10 AS Discount, NULL AS DiscountCategory FROM DUAL UNION ALL
SELECT 'B' AS id, 'XXXB' AS name, 'G040' AS DiscountGroup, 40 AS Discount, 'SOMETXT' AS DiscountCategory FROM DUAL UNION ALL
SELECT 'B' AS id, 'XXXB' AS name, 'T005' AS DiscountGroup, 5 AS Discount, 'HIGH GPA' AS DiscountCategory FROM DUAL
)
SELECT id,
name,
MAX(CASE WHEN DiscountGroup = 'G' THEN Discount ELSE 0 END) AS PermanentDiscount,
MAX(CASE WHEN DiscountGroup = 'G' THEN Cateogry ELSE NULL END) AS Cateogry,
SUM(CASE WHEN DiscountGroup = 'T' THEN Discount ELSE 0 END) AS TempDiscount
FROM ( SELECT id,
name,
SUBSTR(DiscountGroup, 1, 1) AS DiscountGroup,
Discount,
FIRST_VALUE(DiscountCategory) OVER(PARTITION BY id, name, SUBSTR(DiscountGroup, 1, 1) ORDER BY Discount DESC) AS Cateogry
FROM sample_data
)
GROUP BY id, name
ORDER BY id, name;

结果:

I NAME PERMANENTDISCOUNT CATEOGRY TEMPDISCOUNT
- ---- ----------------- -------- ------------
A XXX                 25 MANGMNT            15
B XXXB                40 SOMETXT             5

Answer 3

我想它应该适合你=>

WITH CTE AS(
SELECT ID,SUM(Discount) TempDiscount
FROM tableA 
WHERE DiscountGroup LIKE 'T%' 
GROUP BY ID )
SELECT a.*,c.TempDiscount FROM
CTE c
INNER JOIN tableA a ON a.ID=c.ID AND a.DiscountGroup LIKE 'G%'

注意:你可以在这里查看我的代码=>链接

Answer 4

我在snowflake中运行这个。

select id
,max(Name)
,sum(case when discountgroup like 'G%' then discount else 0 end) Permanentdiscount
,max(case when discountgroup like 'G%' then discountcategory end ) category
,sum(case when discountgroup like 'T%' then discount else 0 end) Temporarydiscount
from SAMPLE_DATA
group by id;