1

I have the following problem:

Apple  | Document 1 | 5
Orange | Document 2 | 4
Apple  | Document 3 | 7

What I would like to happen with a query is the following:

Apple  | Document 1 | Document 3 | 12
Orange | Document 2              | 7

Is there a function that does this for you?

Salman A
  • 262,204
  • 82
  • 430
  • 521
bohzwah
  • 77
  • 7

2 Answers2

2

In SQL Server 2017 you can use STRING_AGG function along with GROUP BY:

SELECT fruit, STRING_AGG(document, '|') AS documents, SUM(value) AS total
FROM t
GROUP BY fruit
Salman A
  • 262,204
  • 82
  • 430
  • 521
1

You can try use combination of Stuff and XML Path if you are working on Pre SQL Server 2017

SELECT fruit, 
    abc = STUFF(
                 (SELECT '|' + doc FROM test t1
                     WHERE t1.fruit = t2.fruit FOR XML PATH ('')), 1, 1, ''
               ), SUM(value)
FROM test t2 GROUP BY fruit

http://sqlfiddle.com/#!18/ce52e/2

The STUFF and XML PATH is very explained well over here

How Stuff and 'For Xml Path' work in Sql Server

Standin.Wolf
  • 1,224
  • 1
  • 10
  • 32
  • Great, I'll give this a shot in a few days. Thanks. Is there a specific terminology, or is there a name to this movement that would better aid my google-fu? – bohzwah Dec 11 '19 at 18:36