How to concatenate items not from main transaction table, but from detail table

Question

I have transaction table proposal_header and the detail table is proposal_item, what I need is to get concatenated tender_number grouped by detail_number in proposal_item, but I don't get it how to concatenate it, how the query should be?

the proposal_header table is :

id  tender_number
7   BS7
12  BS12
14  CS14

the proposal_item table is:

proposal_header_id  detail_number
  7                161932
  7                161929
  12               161932
  12               161929
  14               334659

and then i have joined:

select b.id as mainId,a.detail_number ,b.tender_number
from dbo.proposal_header b
inner join dbo.proposal_item a on a.proposal_header_id =b.id

the result of join is:

main Id  detail_number  tender_number
7        161932         BS7
7        161929         BS7
12       161932         BS12
12       161929         BS12
14       334659         CS14

but what i need, is only detail number and concate tender number, is there a solution to make it like this ?

detail_number  tender_number
161932          BS7, BS12
161929          BS7, BS12
334659          CS14

which db brand you are uisng ....mysql ? sql server .... ?? – ScaisEdge Jan 19 '19 at 13:25 — ScaisEdge, Jan 19 '19 at 13:25

GMB · Answer 1 · 2019-01-20T01:49:31.197

0

You would need to use an aggregate query, along with a string aggregation function. The actual function depends on your RDBMS :

oracle : LISTAGG
mysql : GROUP_CONCAT
sql-server >= 2017 : STRING_AGG
postgres : STRING_AGG

Here is an example for mysql :

select 
    a.detail_number,
    group_concat(
        b.tender_number
        order by b.tender_number separator ', '
    ) as tender_numbers
from 
    dbo.proposal_header b
    inner join dbo.proposal_item a 
        on a.proposal_header_id =b.id
group by a.detail_number

Using sql-server < 2017 :

SELECT
    pi.detail_number,
    tender_numbers = STUFF(
        (
            SELECT ',' + ph.tender_number
            FROM dbo.dbo.proposal_header AS ph
            WHERE ph.id = pi.proposal_header_id
            ORDER BY ph.tender_number
        ).value('.', 'varchar(max)'),
        1, 
        1,
        ''
    )
FROM dbo.proposal_item AS pi
GROUP BY pi.detail_number

edited Jan 20 '19 at 01:49

answered Jan 19 '19 at 13:55

GMB

216,147
25
84
135

sadly i'm using sql server 2012, is there any way to solve it in sql server 2012? – Giri22 Jan 19 '19 at 14:27
@Giri22 : I updated my answer wih a query for sql-server < 2017 – GMB Jan 20 '19 at 01:50
i have tried your query, but it said "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." – Giri22 Jan 20 '19 at 03:29

score 0 · Answer 2 · answered Jan 19 '19 at 15:31

0

Unfortunately in SQL Server 2012, you have to use workarounds. The best SO thread is this:

https://stackoverflow.com/a/31212041/27876

answered Jan 19 '19 at 15:31

Jon Wilson

726
1
8
23

yeah i have read it before, but my case is seems diferent, because i have to group based on detail item with different main id. Because when i group it by detail item, the foreign key have to be grouped to, and it will make duplicate detail item – Giri22 Jan 20 '19 at 03:44

How to concatenate items not from main transaction table, but from detail table

2 Answers2