SQL Server concatenation of records with aggregate function in select statement

Question

I have a view vw_XC_DocInfo_1 with columns VId,VName,DocId,Amount,INum. Below is 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 output like 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 have tried different ways but unable to include aggregate function with STUFF function, please find the query I have tired.

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

Feel free to comment if I confused, Im a newbee couldn't post a question with good formatting.Apolozies for that. — Ganesh Basava, Mar 22 '12 at 20:09
What are the rules for deciding which rows go in the same group? — Mark Byers, Mar 22 '12 at 20:10
do you always need to aggregate only two rows?, and where did you get the `Vid` 3? — Lamak, Mar 22 '12 at 20:11
@MarkByers There are no rules, If there are more than 2 records with same vendorid we need to group those 2 records, For example VendorId 1 has 9 records we need to group in to 2,2,2,2,1.DocIds and InvNum should not be repeated in the each group. — Ganesh Basava, Mar 22 '12 at 20:15
Why do you want to "group" into a concatenated string? Do that in your presentation layer. — JNK, Mar 22 '12 at 20:17
I need wrap this logic into Stored Procedure, also I will be using this SP for report using SSRS. — Ganesh Basava, Mar 22 '12 at 20:20
@Lamak I will pass VID as a comma separated string to this Logic. — Ganesh Basava, Mar 22 '12 at 20:22

score 3 · Answer 1 · answered Mar 22 '12 at 21:42

3

How about something slightly 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 here.

answered Mar 22 '12 at 21:42

Joachim Isaksson

176,943
25
281
294

Thanks a million for the answer, is there anyway can we dyanamically control no of records in groupby.For example if we have 10 records for a single vendorid . We need to group it as 4,4,2(remaining)nabove query restricted to 2,2,2,2,2.I need to pass this 4 as parameter.Is this possible. Thanks a lot again. – Ganesh Basava Mar 22 '12 at 22:32
@GaneshBasava You just explained how they would always be grouped in 2. Now you ask a totally different question. – t-clausen.dk Mar 23 '12 at 08:49

Francis P · Answer 2 · 2012-03-22T20:47:33.853

1

Would that 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 many mistakes in my typing...I think I got it thanks to Joachim Isaksson

edited Mar 22 '12 at 20:47

answered Mar 22 '12 at 20:24

Francis P

13,377
3
27
51

I need to Group the result set with dynamic lists generated above using stuff function.It wont work, I tried it too.Anyway thans for your time. – Ganesh Basava Mar 22 '12 at 20:29
You're right...I had forgotten the group by clause. Does my edited answer work? (If not, can you provide the results generated / or error message) – Francis P Mar 22 '12 at 20:33
It's close, but op wants to concatenate a maximum of two values per row. – Lamak Mar 22 '12 at 20:47
@JoachimIsaksson This is awesome, thanks for that. FrancisP If we execute the query above, the records count is equal to number of DocId as we are grouping by Docid too. – Ganesh Basava Mar 22 '12 at 20:48

score 0 · Answer 3 · edited May 23 '17 at 10:08

0

you could look at coalesce - http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/

but after a brief hunt - I think the question here will help more: Concatenate many rows into a single text string?

Look at the answer by Ritesh that uses XML_Path

edited May 23 '17 at 10:08

Community

1
1

answered Mar 22 '12 at 20:21

user158017

2,891
30
37

Thanks for your time, I'm able to concatinate using STUFF function.However I'm unable to include SUM(AMT) an aggregate function in the main query. – Ganesh Basava Mar 22 '12 at 20:25
it took me a while to realise that this is just an obfuscated form of recursion - but might be very useful if needed ;) – Aprillion Mar 22 '12 at 20:28
I have edited my answer - I did a little more hunting and realized coalesce might help a bit but xml_path is a better option. – user158017 Mar 22 '12 at 20:32
@sql_mommy I have gone through the post mentioned above, Ritesh's solution works only when we do not have aggregate functions in main select query.Problem is not about concatenating, we are hunting to find a solution with group by. – Ganesh Basava Mar 22 '12 at 20:56

score 0 · Answer 4 · answered Mar 23 '12 at 08:50

;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

SQL Server concatenation of records with aggregate function in select statement

4 Answers4