Concatenate SQL Server Records Using Aggregate Function in Select Statement
I have a vw_XC_DocInfo_1 view with VId, VName, DocId, Amount, INum columns. Below are the table data.
Vid VName DocId Amount INum
1 ABC 10 100 INV1
1 ABC 11 10 INV2
1 ABC 12 20 INV3
1 ABC 13 30 INV4
2 XYZ 14 200 INV5
2 XYZ 15 10 INV6
2 XYZ 16 20 INV7
2 XYZ 17 30 INV8
I need to display the output as shown below.
Vid VName DocIdsList Amount INumList
1 ABC 10,11 110 INV1,INV2
1 ABC 12,13 50 INV3,INV4
2 XYZ 14,15 210 INV5,INV6
3 XYZ 16,17 50 INV7,INV8
I tried different ways but couldn't enable aggregated function with STUFF function, please find the query I'm tired of.
with CTE
as (
select top 20 V.VendorId,
V.VendorName,
STUFF((
select top 3 ',' + CONVERT(varchar(MAX), V1.DocumentId)
from vw_XC_DocInfo_1 V1
where V1.VendorID = V.VendorId
order by V1.DocumentId
for xml PATH('')
), 1, 1, '') as DocIdsList,
STUFF((
select top 3 ',' + CONVERT(varchar(MAX), V1.InvoiceNumber)
from vw_XC_DocInfo_1 V1
where V1.VendorID = V.VendorId
order by V1.InvoiceNumber
for xml PATH('')
), 1, 1, '') as InvNumList
from vw_XC_DocInfo_1 V
order by V.VendorID
)
select VendorId,
VendorName,
DocIdsList,
InvNumList
from CTE
group by VendorId,
VendorName,
DocIdsList,
InvNumList
+3
source to share
4 answers
How about something a little more outside the box?
SELECT VendorId, VendorName,
CASE WHEN COUNT(DocumentId)>1
THEN CAST(MIN(DocumentId) AS VARCHAR(MAX)) + ',' +
CAST(MAX(DocumentId) AS VARCHAR(MAX))
ELSE CAST(MIN(DocumentId) AS VARCHAR(MAX))
END AS DocIdList,
SUM(Amount) Amount,
CASE WHEN COUNT(InvoiceNumber)>1
THEN MIN(InvoiceNumber) + ',' + MAX(InvoiceNumber)
ELSE MIN(InvoiceNumber)
END AS INumList
FROM
(SELECT *,(ROW_NUMBER() OVER (PARTITION BY VendorId
ORDER BY VendorId) - 1) / 2 AS seq
FROM vw_XC_DocInfo_1) AS result
GROUP BY VendorId, VendorName, seq
Demo is here .
+3
source to share
Will this work for you?
SELECT V.VendorId,
V.VendorName,
STUFF((
select ',' + CONVERT(varchar(MAX), V1.DocumentId)
from vw_XC_DocInfo_1 V1
where V1.VendorID = V.VendorId
order by V1.DocumentId
for xml PATH('')
), 1, 1, '') as DocIdsList,
SUM(V.Amount) as AmountSums,
STUFF((
select ',' + CONVERT(varchar(MAX), V1.InvoiceNumber)
from vw_XC_DocInfo_1 V1
where V1.VendorID = V.VendorId
order by V1.InvoiceNumber
for xml PATH('')
), 1, 1, '') as InvNumList
FROM vw_XC_DocInfo_1 V
GROUP BY V.VendorId, V.VendorName
ORDER BY V.VendorId, V.VendorName
Sorry I had a lot of mistakes while typing ... I think I got it thanks to Joachim Isaksson
+1
source to share
you could look at coalesce - http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/
but after a short hunt - I think there will be more help here: Combine multiple lines into one text string?
Have a look at Ritesh's answer which uses XML_Path
0
source to share
;with a
as
(
select Vid,VName,DocId,Amount,INum,
row_number() over(partition by vid order by Inum) n
from vw_XC_DocInfo_1
)
select a.Vid, a.vname,
cast(a.docid as varchar(3)) + coalesce(','+cast(b.docid as varchar(9)), '') docid,
a.amount + coalesce(b.amount, 0) amount,
a.INum + coalesce(',' + b.INum, '') INumList
from a left join a b on a.n + 1 = b.n and a.vid = b.vid
where a.n%2 = 1
0
source to share