Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – Aggregate all rows in a table matching a condition to a single string

I have the following 2 tables:

Person
------
PersonId int PK
Name varchar
ThingCollectionId int
=======================
PersonId    Name    ThingCollectionId
1           Joe     100
2           Mary    101
3           Dave    102


Thing
-----
ThingId int PK
ThingCollectionId int
Name varchar
=======================
ThingId ThingCollectionId   Name
1       101                 Baseball
2       102                 Basketball
3       102                 Football

Each row in the Thing table represents a single object. Objects are grouped into Collections, as identified by the ThingCollectionId. Each Person is associated with a single Thing Collection.

I need a query that aggregates the names of all things in the collection associated with the row in the Person table. Note that there may be an arbitrary number of things in the collection, from 0 to any positive number. The results I would like to get from this query look like this:

PersonId    Name    Things
1           Joe     [NULL]
2           Mary    Baseball
3           Dave    Basketball,Football

Is it possible to get what I’m looking for with a single query?

I am using SQLServer 2022:

Microsoft SQL Server 2022 (RTM-CU13) (KB5036432) - 16.0.4125.3 (X64) 
    May  1 2024 15:05:56 
    Copyright (C) 2022 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 22.04.4 LTS) <X64>
Tags:

2

Answers


  1. 0

    Yes, it is possible. You can use string_agg which is SQL Servers take on listagg

    SELECT p.personid, p.name
     , string_agg(t.name, '')
FROM person p
LEFT JOIN thing t
   USING (thingcollectionid)
GROUP BY p.personid, p.name

    Untested, but it should be fairly close

    If SQL Server does not support the using clause, it can be replaced with

     ON t.thingcollectionid = p.thingcollectionid
    Login or Signup to reply.
  2. 0

    I find OUTER APPLY quite useful for this scenario, it avoids you having to perform any fake grouping:

    SELECT p.personid, p.name, x.sagg
  FROM dbo.Person AS p
 OUTER APPLY
 (
   SELECT STRING_AGG(Name, ',')
     FROM dbo.Thing 
    WHERE ThingCollectionId = p.ThingCollectionId
  ) AS x(sagg);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search