sql multiple column concatenation using stuff

Question

i have two columns invoice status and vendor number.i want to concatenate invoice status into one row based on vendor number using stuff

vendor num    invoice status
70000141      V-Parked Items
70000141      V-Parked Items
70000141      not available
70000141      s-noted items
77000141      V-Parked Items

output should be:

vendor num    invoice status
70000141      V-Parked Items,s-noted items,not avilable

can any one help in this

Which database are you using? There are many you know. – Salman A Sep 05 '18 at 11:32 — Salman A, Sep 05 '18 at 11:32
I am using sql server – cherrycharitha Sep 05 '18 at 12:20 — cherrycharitha, Sep 05 '18 at 12:20

score 4 · Answer 1 · answered Sep 05 '18 at 11:35

4

stuff() is available in SQL Server so, i would do :

select vendor_num, stuff( (select distinct ','+t1.invoice_status
                           from table t1
                           where t1.vendor_num = t.vendor_num 
                           for xml path('')
                          ), 1, 1, ''
                        ) as invoice_status
from table t
group by vendor_num;

answered Sep 05 '18 at 11:35

Yogesh Sharma

49,870
5
26
52

Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "dbo.table4.VendorNumber" could not be bound. this is the error i got while trying above code.table which has columns is dbo.table2 – cherrycharitha Sep 05 '18 at 12:19
@cherry. . . Why you choose reference from `table4.VendorNumber` which is already available from `table2.VendorNumber` ?? – Yogesh Sharma Sep 05 '18 at 12:28
I am a bit confused from above code what are t1 and t with that i tried table4 what are t1 and t in above code can u please share – cherrycharitha Sep 05 '18 at 12:31
@cherry. . . As per your sample query `t` is a `dbo.table2 t` & `t1` is a `dbo.table2 t`` for `subquery` available in `stuff()` function. – Yogesh Sharma Sep 05 '18 at 12:36

score 0 · Answer 2 · edited Sep 05 '18 at 13:26

0

I am using sql management studio 2014. I tried using this code but ended up with an error:

select VendorNumber, stuff( (
    select distinct ','+dbo.table2.InvoiceStatus
    from dbo.table2
    where dbo.table4.VendorNumber = dbo.table2.VendorNumber 
    for xml path('')
    ), 1, 1, ''
) as InvoiceStatus
from dbo.table2
group by VendorNumber

But ended up with error Msg 4104, Level 16, State 1, Line 3:

The multi-part identifier "dbo.table4.VendorNumber" could not be bound.

The table which have columns is dbo.table2.

edited Sep 05 '18 at 13:26

cfnerd

3,658
12
32
44

answered Sep 05 '18 at 12:05

cherrycharitha

35
2
9

Please do not add additional information as an answer. [edit](https://stackoverflow.com/posts/52184085/edit) your question instead. – Sep 05 '18 at 12:08

score -1 · Answer 3 · answered Sep 05 '18 at 11:36

-1

use group_concat if mysql

select vendo_num,group_concat(invoice_status separator ',') from tablea
group by vendo_num

answered Sep 05 '18 at 11:36

Zaynul Abadin Tuhin

31,407
5
33
63

sql multiple column concatenation using stuff

3 Answers3