Combine multiple value into 1 for Impala SQL-CodePudding

I want to combine multiple product entries into 1 and also sum their price. Currently, the database looks like this :

Name                Product               Price
Zack                Vanilla Twist         1
Jane                Lolipop               0.5
Zack                Lolipop               0.5
Zack                Candymint             0.5
Jane                ChocoLoco LM          1.5

I want to change the look of this into something like this:

Name                Product                                 sum(Price)
Zack                Vanilla Twist, Lolipop, Candymint       2
Jane                Lolipop, ChocoLoco LM                   2

How to do this using Impala SQL?

CodePudding user response：

declare @temp table (Name varchar(50), product varchar(50), Price decimal(3,1))
insert into @temp values ('Zack','Vanilla Twist',1)
insert into @temp values ('Jane','Lolipop',0.5)
insert into @temp values ('Zack','Lolipop',0.5)
insert into @temp values ('Zack','Candymint',0.5)
insert into @temp values ('Jane','ChocoLoco LM',1.5)

-- No cursor, Whil loop, or User defined function:
SELECT 
  Name,
  STUFF((
    SELECT ', '   product 
    FROM @temp 
    WHERE (name = Results.name) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS Product
  ,sum(Price) as [Sum(Price)]
FROM @temp Results
GROUP BY name

Output:

Name    Product                             Sum(Price)
Jane    Lolipop, ChocoLoco LM               2
Zack    Vanilla Twist, Lolipop, Candymint   2

CodePudding user response：

This query works for MySQL, this might help you.

select Name, group_concat(`product` separator ', ') Product, sum(Price)
from tempt
group by Name
order by Name desc

dbfiddle here