SQL Server : use CTE as subquery to compress rows into concatenated string

Question

I am rewriting several pages of a badly written app, I am trying to incorporate this logic in the main query as a subquery so I can get all the data in 1 swoop instead of connection 50K times like the current code.

I started off with

DECLARE @combinedString NVARCHAR(MAX),@mds   NVARCHAR(MAX)

SELECT 
    @combinedString = COALESCE(@combinedString + ', ', '') + serial,
    @mds = MDS  
FROM 
    (aircraftserials 
LEFT JOIN  
    serialnums ON serialnums.ID = aircraftserials.Serialnum_ID) 
WHERE 
    (85 = aircraftserials.Aircraft_ID AND serial IS NOT NULL)

I want to eliminate these variables and make this a subquery,

I think I need a CTE; end result needs to be

MDS, concatenated serials

I have tried this post but cannot quite get it Simplify CTE string concatenation?

Here is the sample of the data

CREATE TABLE TestBed 
(
     Aircraft_ID INT, 
     Serial VARCHAR(50),
     MDS VARCHAR(50)
)

INSERT INTO TestBed (Aircraft_ID, Serial, MDS) 
VALUES (85, '56-1965', 'T-37B'),
       (85, '56-1967', 'T-37B'),
       (85, '56-3547', 'T-37B'),
       (85, '56-3577', 'T-37B'),
       (85, '57-2265', 'T-37B'),
       (85, '57-2272', 'T-37B'),
       (85, '58-1915', 'T-37B'),
       (85, '58-1925', 'T-37B'),
       (85, '59-0249', 'T-37B'),
       (85, '59-0273', 'T-37B'),
       (85, '59-0299', 'T-37B')

select * from TestBed

Sample data and desired results would really help . . . as would some idea of what the code is supposed to be doing. — Gordon Linoff, Jan 03 '19 at 16:37
Also use proper aliases in your sample query to show which column is coming from which table, for example `serial` and `mds` columns in your select . — M.Ali, Jan 03 '19 at 16:42
I just need help flattening the data in a CTE, i can merge evverything later — China Syndrome, Jan 03 '19 at 16:57
You want that combined string to grow from row to row or just have one row for the aircraft with all serials in a string? — sticky bit, Jan 03 '19 at 17:17
1 row with a concat of all the serials and then the single MDS — China Syndrome, Jan 03 '19 at 17:18
If there are more than one MDS for an aircraft would that string only contain the serials from that MDS or that of all? — sticky bit, Jan 03 '19 at 17:23
There is never more than one MDS or rather there never should be — China Syndrome, Jan 03 '19 at 17:25
@ChinaSyndrome As you probably know, most systems contain a lot of data that "never should be" the way it is, but wasn't prevented from getting that way. Make sure your code won't blow up if, by chance, there's more than one MDS. — Ann L., Jan 03 '19 at 17:47
Unless you know for a fact that the system won't let there be more than one MDS and the database actively prevents it. — Ann L., Jan 03 '19 at 17:48
preaching to the choir here, This DB has no indexes no constraints nothing, I have already scripted out constraints based on what little is documented in the C# code, my hope is they will let me or a proper DBA appress this evil — China Syndrome, Jan 03 '19 at 17:50

sticky bit · Answer 1 · 2019-01-03T17:37:28.563

1

I think you're not looking for a CTE but an aggregation with string concatenation. In SQL Server that can be done using a subquery FOR XML.

SELECT tb1.aircraft_id,
       stuff((SELECT concat(', ', tb2.serial)
              FROM testbed tb2
              WHERE tb2.aircraft_id = tb1.aircraft_id
                    AND tb2.mds = tb1.mds
              FOR XML PATH ('')), 1, 2, '') serial,
       tb1.mds
       FROM testbed tb1
       GROUP BY tb1.aircraft_id,
                tb1.mds;

db<>fiddle

BTW: If mds is always the same for each aircraft, then it shouldn't be in testbed but in the aircraft table.

edited Jan 03 '19 at 17:37

answered Jan 03 '19 at 17:35

sticky bit

36,626
12
31
42

let me plug that into the main query and Ill let ya know if it does the trick – China Syndrome Jan 03 '19 at 17:37

score 0 · Answer 2 · answered Jan 03 '19 at 17:46

I came up with a recursive CTE based on your test data

;with ForceId as 
    (SELECT ROW_NUMBER() OVER(partition by MDS order by serial) as RN,* FROM TestBed),
    RECURS as
    (SELECT f.MDS,f.RN,f.Serial, cast(f.serial as nvarchar(max)) as x  FROM ForceId F WHERE F.RN = 1
     UNION ALL
     SELECT f2.mds,f2.rn, r.Serial,r.x  + ', ' + f2.Serial  FROM ForceId F2 
                    JOIN RECURS R
                    ON F2.mds = R.mds 
                    AND R.RN + 1 = F2.rn        
    )
    SELECT MDS, x  FROM 
            recurs r where r.rn = 
            (select max(r2.rn) from recurs r2 where r.MDS = r2.mds)

SQL Server : use CTE as subquery to compress rows into concatenated string

2 Answers2