Some confusion using STUFF/SELECT ... FOR XML to turn rows into columns

Question

I am trying to make a report from a 3rd party DB of rental records. It is driven by a table, aliased X below, that links a lease, a property, a tenant, and one or more units. For instance, FedEx might have a lease called A.2021 that rents two units, 101, 102 in property "br", resulting in two lines in X. fkeys in X link to various informational tables. My query currently returns:

propcode    tenname unit    area    months  startdt    enddt
br          FE      101     30876   63      2021-08-01 2026-10-31 
br          FE      102     30876   63      2021-08-01 2026-10-31

I would like it to return:

propcode    tenname units   area    months  startdt    enddt
br          FE      101,102 30876   63      2021-08-01 2026-10-31

I cannot create tables (or #temp or cursors etc.), just SELECT, and it's running pre-2017. As I understand it, the canonical method in this case is to use FOR XML PATH and then STUFF to make it into a comma list.

So I began adapting the examples. First I gather keys in an inner query:

hTenant hAmendment  hUnit
45219   1113871     36543
45219   1113871     36544

I then use those keys in an outer query where I have the SELECT/FOR XML:

select p.scode propcode,
       t.slastname tenname,
       a.dcontractarea,
       a.iterm months,
       a.dtstart startdt,
       a.dtend enddt,
       (STUFF((SELECT cast(', ' + ug.scode as varchar(max))
               from unit ug
               where inside.hUnit=ug.hmy
                 and U.HPROPERTY=ug.HPROPERTY
               for xml path('')),1,2,'')) as units
FROM ( select hTenant,
              hAmendment,
              hUnit
       from UNITXREF X
       where dtLeasefrom>'2021-01-01') as inside
      JOIN UNIT U on u.hmy=inside.hUnit
      JOIN PROPERTY P on P.HMY=u.HPROPERTY
      JOIN TENANT T on T.HMYPERSON=inside.hTenant
      JOIN COMMAMENDMENTS A on inside.hAmendment=A.hmy
GROUP BY p.scode,
        t.slastname,
        a.dcontractarea,
        a.iterm,
        a.dtstart,
        a.dtend,
        inside.hUnit,
        u.HPROPERTY

I have tried many variations on this theme - removing the hproperty (which isn't really needed), using the scode instead of the key, etc. All with the same result, multiple rows with one unit per row. I assume the WHERE is failing, perhaps a missing sub, but I can't understand what is supposed to be happening.

What am I missing?

Answer 1

A minimal reproducible example is not provided.

Notable points:

• The CTE simulates a resultset for your multiple tables joins.
• The main SELECT has 'p' alias for a parent, 'c' alias for a child rows with units.
• We are using GROUP BY clause on the p(arent) level to reduce multiple rows to one.
• WHERE p.propcode = c.propcode clause simulates join between p(arent) and c(hild).

SQL

;WITH rs AS
(
    SELECT *
    FROM (VALUES
        ('br', 'FE', '101', 30876, 63, '2021-08-01', '2026-10-31'),
        ('br', 'FE', '102', 30876, 63, '2021-08-01', '2026-10-31')
    ) AS t(propcode, tenname, unit, area, months, startdt, enddt)
)
SELECT propcode, tenname 
, (STUFF((SELECT ', ' + c.unit
               from rs AS c -- c(hild)
               where p.propcode = c.propcode
                 and p.tenname = c.tenname
                 and p.area = c.area
                 and p.months = c.months
                 and p.startdt = c.startdt
                 and p.enddt = c.enddt
               for xml path('')),1,2,'')) as units
, area, months, startdt, enddt
FROM rs AS p    -- p(arent)
GROUP BY propcode, tenname, area, months, startdt, enddt;

Output

propcode	tenname	units	area	months	startdt	enddt
br	FE	101, 102	30876	63	2021-08-01	2026-10-31