0

enter image description here

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

LukStorms
  • 28,916
  • 5
  • 31
  • 45
Mumhammad Mumtaz Ali
  • 31
  • 3
  • i need select query to get required result – Mumhammad Mumtaz Ali Nov 18 '21 at 07:45
  • 4
    which database brand? – edi Nov 18 '21 at 07:47
  • 1
    Hint: `GROUP BY`. – jarlh Nov 18 '21 at 07:49
  • Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Nov 18 '21 at 08:13
  • Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) – Mumhammad Mumtaz Ali Nov 18 '21 at 08:35
  • I am using SQL Server and i want to make view by using above query – Mumhammad Mumtaz Ali Nov 18 '21 at 08:36
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Nov 18 '21 at 08:41
  • 1
    When asking your next question, add the tag from the beginning. Don't waste other people's time writing anwers for other dbms's. – jarlh Nov 18 '21 at 08:42
  • sorry for wasting your valuable time sorry again.. – Mumhammad Mumtaz Ali Nov 18 '21 at 08:43
  • 1
    As you're using 2014 you'll need to use the old `FOR XML PATH` solution, demonstrated [here](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017) and [here](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Thom A Nov 18 '21 at 09:01

4 Answers4

2

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
LukStorms
  • 28,916
  • 5
  • 31
  • 45
1

Solution in PostgreSQL. Please note distinct item.

select string_agg(distinct item, '|') items, supplier, 
       sum(qty) qty, sum(st) st, sum(amt) amt
from the_table 
group by supplier;

Edit
My answer above is not relevant anymore after sql-server DB was tagged.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
1

You can use this for SQL SERVER < 2017

SELECT 
Supplier
,Items = STUFF((
        SELECT ' | ' + t2.Item
        FROM TestTable t2
        WHERE t.Supplier = t2.Supplier
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
,SUM(Qty) Qty
,SUM(ST) ST
,SUM(Amt) Amt
FROM TestTable t
GROUP BY Supplier
Dale K
  • 25,246
  • 15
  • 42
  • 71
itsmetonton
  • 56
  • 2
0

You should really learn more about SQL and read the documentation for your concrete database type - SQL Queries varies slightly for different databases. Nevertheless in MySQL you could do:

SELECT GROUP_CONCAT(Item SEPARATOR ' | '), SUM(Qty), SUM(ST), SUM(Amt) FROM <supplier> GROUP BY Supplier;

Where <supplier> should be replaced by the name of your table.

edi
  • 917
  • 7
  • 17