Select Query for Sum and group it

Question

I am confused to getting the merge the item column. Please help me to do this.

Answer 1

Solution in Oracle/DB2/Snowflake SQL:
(The LISTAGG function is ANSI/SQL:2016 compliant, but not generally supported in every recent RDBMS version)

SELECT
 LISTAGG(Item, '|') WITHIN GROUP (ORDER BY Qty) AS Item, 
 Supplier, 
 SUM(Qty) AS Qty, 
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable 
GROUP BY Supplier
ORDER BY Supplier

Solution for MS Sql Server 2014 :

SELECT
 STUFF((select '|'+ t2.Item
        from yourtable t2
        where t2.Supplier = t.Supplier
        order by t2.Qty
        for xml path ('')),1,1,'') AS Item,
 Supplier, 
 SUM(Qty) AS Qty,
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable t
GROUP BY Supplier
ORDER BY Supplier

Solution for MS Sql Server 2017+ using STRING_AGG :

SELECT
 STRING_AGG(Item, '|') WITHIN GROUP (ORDER BY Qty) AS Item,
 Supplier, 
 SUM(Qty) AS Qty,
 SUM(St) AS St, 
 SUM(Amt) AS Amt
FROM yourtable t
GROUP BY Supplier
ORDER BY Supplier