Roll up multiple rows into one when joining in SQL Server

Question

I have a table, Foo

ID | Name
-----------
1  | ONE
2  | TWO
3  | THREE

And another, Bar:

ID | FooID | Value
------------------
1  | 1     | Alpha
2  | 1     | Alpha
3  | 1     | Alpha
4  | 2     | Beta
5  | 2     | Gamma
6  | 2     | Beta
7  | 3     | Delta
8  | 3     | Delta
9  | 3     | Delta

I would like a query that joins these tables, returning one row for each row in Foo, rolling up the 'value' column from Bar. I can get back the first Bar.Value for each FooID:

SELECT * FROM Foo f OUTER APPLY
(
    SELECT TOP 1 Value FROM Bar WHERE FooId = f.ID
) AS b

Giving:

ID | Name  | Value
---------------------
1  | ONE   | Alpha
2  | TWO   | Beta
3  | THREE | Delta

But that's not what I want, and I haven't been able to find a variant that will bring back a rolled up value, that is the single Bar.Value if it is the same for each corresponding Foo, or a static string something like '(multiple)' if not:

ID | Name  | Value
---------------------
1  | ONE   | Alpha
2  | TWO   | (multiple)
3  | THREE | Delta

I have found some solutions that would bring back concatenated values (albeit not very elegant) 'Alpha' Alpha, Alpha', 'Beta, Gamma, Beta' &c, but that's not what I want either.

You can try using string_agg if your SQL Server versino is at least 2017 as mentioned here : https://stackoverflow.com/questions/15477743/listagg-in-sqlserver For your query it could be : Select Foo.Id, Foo.Name, string_agg(Bar.Value,'') From Foo, Bar Where Foo.ID = Bar.FooID Group by Foo.ID, Foo.Name — planben, Aug 19 '20 at 16:24

score 1 · Accepted Answer · answered Aug 19 '20 at 15:18

One method, using a a CASE expression and assuming that [Value] cannot have a value of NULL:

WITH Foo AS
    (SELECT *
     FROM (VALUES (1, 'ONE'),
                  (2, 'TWO'),
                  (3, 'THREE')) V (ID, [Name])),
Bar AS
    (SELECT *
     FROM (VALUES (1, 1, 'Alpha'),
                  (2, 1, 'Alpha'),
                  (3, 1, 'Alpha'),
                  (4, 2, 'Beta'),
                  (5, 2, 'Gamma'),
                  (6, 2, 'Beta'),
                  (7, 3, 'Delta'),
                  (8, 3, 'Delta'),
                  (9, 3, 'Delta')) V (ID, FooID, [Value]))
SELECT F.ID,
       F.[Name],
       CASE COUNT(DISTINCT B.[Value]) WHEN 1 THEN MAX(B.Value) ELSE '(Multiple)' END AS [Value]
FROM Foo F
     JOIN Bar B ON F.ID = B.FooID
GROUP BY F.ID,
         F.[Name];

Jignesh Bhayani · Answer 2 · 2020-08-20T06:05:14.933

0

You can also try below:

SELECT F.ID, F.Name, (case when B.Value like '%,%' then '(Multiple)' else B.Value end) as Value
FROM Foo F
outer apply
(
select SUBSTRING(( 
                    SELECT distinct  ', '+  isnull(Value,',') FROM Bar WHERE FooId = F.ID
                    FOR XML PATH('') 
                    ), 2 , 9999) as Value
) as B

edited Aug 20 '20 at 06:05

answered Aug 19 '20 at 15:22

Jignesh Bhayani

83
3

1

This does not answer the question as asked, as the value `(multiple)` is desired and not a list of all values. – iamdave Aug 19 '20 at 15:23

Roll up multiple rows into one when joining in SQL Server

2 Answers2