A colleague posed a question, and I thought I’d post the answer here in case I needed to find it in the future, or others needed something like it. I found similar examples on the internet, but not quite like this, where a table needed to be filtered and ordered for the column that was merged, while listing other columns from the initial table. If the subquery filtering for tax returns blank or nothing, still return ‘SALES TAX’. Combine them with a slash. Here is the result using a memory table.
-- Create the memory table DECLARE @PJInvDet TABLE ( draft_num varchar(10), acct varchar(15), li_type varchar(1) )
-- Provide sample data INSERT @PJInvDet VALUES ('01', 'SALES TAX', 'T') INSERT @PJInvDet VALUES ('01', 'NMGRT', 'T') INSERT @PJInvDet VALUES ('02', 'SALES TAX', 'T') INSERT @PJInvDet VALUES ('03', 'NMGRT', 'T') INSERT @PJInvDet VALUES ('04', '', 'T') INSERT @PJInvDet VALUES ('05', 'NOTHING FOUND', 'X')
-- Query the data SELECT T.draft_num, tax_list = COALESCE( -- This covers draft_num 05, where the subquery returns null. SUBSTRING( (SELECT '/' + CASE WHEN LEN(acct) = 0 THEN 'SALES TAX' ELSE acct END -- This covers draft_num 04, where the acct is blank. FROM @PJInvDet WHERE draft_num = T.draft_num AND li_type = 'T' GROUP BY acct ORDER BY acct DESC FOR XML PATH('')), 2, 20000), 'SALES TAX') FROM @PJInvDet T GROUP BY T.draft_num
Here’s the output:
draft_num tax_list 01 SALES TAX/NMGRT 02 SALES TAX 03 NMGRT 04 SALES TAX 05 SALES TAX