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?

Some DDL and DML for your tables and sample data would help us here; the only sample data we have is the non-aggregated result set which doesn't help us understand the data or the relationships. — Thom A, Mar 24 '23 at 15:56
While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. — Yitzhak Khabinsky, Mar 24 '23 at 16:01
Unfortunately the tables in question average 50 to 70 columns and the data is confidential. I am not sure what to do. — Maury Markowitz, Mar 24 '23 at 16:24
@ThomA - a question, what do you mean by "non aggregated"? Perhaps this is the crux of the problem. I think I could solve this if I understood what is supposed to be happening, but none of the pages I found explaining this technique do that, they simply say "do this" with no explanation. Is the data in the inner supposed to be aggregated? If so, how? — Maury Markowitz, Mar 24 '23 at 16:29
It seems that you grouped by `inside.hUnit`, but this is the value that you want to aggregate. Try to remove it from the `group by` clause. And probably you must do the same with `u.HPROPERTY` — Roberto Ferraris, Mar 24 '23 at 16:32
"non-aggregated" means it's not aggregated, @MauryMarkowitz . — Thom A, Mar 24 '23 at 16:35
*"none of the pages I found explaining this technique"* Explain *what* technique? Using `FOR XML PATH` for string aggregation? Have a look at [my answer](https://stackoverflow.com/a/64227488/2029983) where I explain the syntax in more detail than just "do this". — Thom A, Mar 24 '23 at 16:37
@RobertoFerraris- removing the group by on hunit cause the SQL to fail as it is not allowed in the SELECT. I did try that. — Maury Markowitz, Mar 24 '23 at 17:04

Yitzhak Khabinsky · Answer 1 · 2023-03-24T18:00:50.487

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

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

1 Answers1