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

Thanks to Pinal Dave of the SQL Authority for the idea to use XML Path.