1

I'm building an SQL query to group and aggregate data. The result should have sum for two decimal fields and comma-delimited varchar field:

┌────┬───────────────┬─────────────┬──────────┬────────┬─────┬─────────────┐
│ Id │ InvoiceNumber │ InvoiceDate │ SellerId │ Amount │ Tax │ InvoiceType │
├────┼───────────────┼─────────────┼──────────┼────────┼─────┼─────────────┤
│  1 │ a-001         │ 2019-01-11  │        1 │     10 │   2 │ Regular     │
│  2 │ a-002         │ 2019-01-12  │        1 │     10 │   2 │ Regular     │
│  3 │ a-003         │ 2019-01-13  │        1 │     10 │   2 │ Special     │
│  4 │ a-004         │ 2019-01-14  │        2 │     10 │   2 │ Regular     │
│  5 │ a-005         │ 2019-01-15  │        2 │     10 │   2 │ Regular     │
│  6 │ a-006         │ 2019-01-16  │        3 │     10 │   2 │ Special     │
└────┴───────────────┴─────────────┴──────────┴────────┴─────┴─────────────┘

Desired output:

┌────┬───────────────┬──────────┬───────────┬────────┬─────┬─────────────────┐
│ Id │ InvoiceNumber │ SellerId │ RowNumber │ Amount │ Tax │   InvoiceType   │
├────┼───────────────┼──────────┼───────────┼────────┼─────┼─────────────────┤
│  3 │ a-003         │        1 │         1 │     30 │   6 │ Regular,Special │
│  5 │ a-005         │        2 │         1 │     20 │   4 │ Regular         │
│  6 │ a-006         │        3 │         1 │     10 │   2 │ Special         │
└────┴───────────────┴──────────┴───────────┴────────┴─────┴─────────────────┘

So far I tried:

SELECT [Id],
    [InvoiceNumber],
    [SellerId],
    ROW_NUMBER() OVER (PARTITION BY [SellerId] ORDER BY [InvoiceDate] DESC) AS [RowNumber],
    SUM([Amount]) OVER (PARTITION BY [SellerId]) AS [Amount], 
    SUM([Tax]) OVER (PARTITION BY [SellerId]) AS [Tax],
    STRING_AGG([InvoiceType], ',')
        OVER (PARTITION BY [SellerId] ORDER BY [InvoiceType]) AS [InvoiceTypes]
FROM [Invoices]
WHERE [RowNumber] = 1

However, this doesn't work in SQL Server 2012 as it doesn't support the STRING_AGG() function. Plus I need DISTINCT values to avoid duplicates in the joined string. Is there any way to accomplish this?

NOTE: the problem is not to concatenate, the problem is to concatenate in a window function.

ttaaoossuuuu
  • 7,786
  • 3
  • 28
  • 58
  • This is not a duplicate of "Comma separated results in SQL", I know `STUFF` + `FOR XML` trick. The problem here is to concatenate results in `window function`. Please reopen. – ttaaoossuuuu Jan 24 '19 at 12:14
  • Then you need to upgrade to SQL Server 2017 and use `STRING_AGG`, as you have done. You can't use a function that doesn't exist on the version of SQL Server you are using, and thus the solution is the one I've marked as the duplicate. You have to use `STUFF` and `FOR XML PATH`. You can't use `OVER` (and `STRING_AGG`) to create a delimited list in SQL Server 2012. – Thom A Jan 24 '19 at 12:18
  • `STUFF` and `FOR XML PATH` cannot be used with `PARTITION BY`. I have asked for an alternative available in my environment. And the linked answer does not provide it. Sorry, but "upgrade to SQL Server 2017" suggestion is on par with "consider changing jobs". – ttaaoossuuuu Jan 24 '19 at 12:23
  • Because you need to use a correlated subquery, which is still part of the `STUFF` and `FOR XML PATH` solution. Sounds like you were omitting the `WHERE` in your subquery. This is still very much a duplicate. – Thom A Jan 24 '19 at 12:24
  • A recursive CTE can be used to get the comma seperated values and later can be joined with the result set – Dheerendra Jan 24 '19 at 12:36

1 Answers1

2
SELECT DISTINCT Res1.ID
       ,Res1.InvoiceDate
       ,Res1.SellerID
       ,Res1.Amount
       ,Res1.Tax
       ,(SELECT TOP 1 InvoiceNumber FROM [Invoices] WHERE SellerID = Res1.SellerID ORDER BY InvoiceDate DESC) AS InvoiceNumber
       ,(SELECT STUFF((SELECT DISTINCT ','+InvoiceType
         FROM [Invoices]
         WHERE SellerID = Res1.SellerID
         FOR XML PATH('')),1,1,'')
        ) AS InvoiceType
       FROM 
(
SELECT MAX(ID) AS ID
       ,MAX(InvoiceDate) AS InvoiceDate
       ,SellerID
       ,SUM(Amount) AS Amount
       ,SUM(Tax) AS Tax
FROM [Invoices]
GROUP BY [SellerId]
)Res1
Dheerendra
  • 284
  • 1
  • 7