SQL技术问询：业务术语关联多值字段多行合并为单行拼接方法

Hey there! Let's figure out how to turn those multiple rows for each business term into a single row with comma-separated values for synonyms and other multi-value fields. Since your script uses dbo.TblBusinessTerm, I'll start with SQL Server solutions (the most likely fit here) and toss in quick tips for other common databases too.

If you're using SQL Server 2017 or newer #

The easiest way is to use the built-in STRING_AGG() function—it's designed exactly for this kind of row-to-column concatenation. Here's how to adapt your script:

SELECT 
    bt.BusinessTerm,
    bt.BusinessTermLongDesc, -- Replace with your actual long description field name
    STRING_AGG(s.Synonym, ', ') AS Synonyms, -- Replace s.Synonym with your synonym field/table
    -- Repeat STRING_AGG for any other multi-value fields you need to concatenate
FROM 
    dbo.TblBusinessTerm bt
LEFT JOIN 
    dbo.TblSynonyms s ON bt.BusinessTermID = s.BusinessTermID -- Adjust join key to match your schema
GROUP BY 
    bt.BusinessTerm, bt.BusinessTermLongDesc -- Include all non-aggregated fields here

A few quick notes on this:

• STRING_AGG() takes two arguments: the field you want to concatenate, and the separator (in this case, ', ').
• It automatically ignores NULL values, so if a business term has no synonyms, the Synonyms column will just be NULL (you can use COALESCE(STRING_AGG(...), 'No synonyms') to replace that with a friendly message if needed).
• If you have duplicate synonyms and want to deduplicate them, add DISTINCT inside the function: STRING_AGG(DISTINCT s.Synonym, ', ').

For older SQL Server versions (pre-2017) #

If you're stuck on an older version that doesn't support STRING_AGG(), you can use the FOR XML PATH trick to achieve the same result:

SELECT 
    DISTINCT
    bt.BusinessTerm,
    bt.BusinessTermLongDesc,
    STUFF(
        (SELECT ', ' + s.Synonym
         FROM dbo.TblSynonyms s
         WHERE s.BusinessTermID = bt.BusinessTermID -- Match the parent row's business term
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 2, '' -- Remove the leading ', ' from the concatenated string
    ) AS Synonyms
FROM 
    dbo.TblBusinessTerm bt

How this works:

• The subquery uses FOR XML PATH('') to stitch all synonyms for a business term into a single string (like ', Synonym1, Synonym2').
• STUFF() cuts off the first two characters (the leading ', ') to clean up the result.
• The TYPE and .value('.', 'NVARCHAR(MAX)') parts ensure special characters (like < or &) aren't escaped into XML entities.

If you're not using SQL Server, here's how to do the same in other popular systems:

• MySQL/MariaDB: Use GROUP_CONCAT():

  SELECT 
      bt.BusinessTerm,
      bt.BusinessTermLongDesc,
      GROUP_CONCAT(s.Synonym SEPARATOR ', ') AS Synonyms
  FROM 
      TblBusinessTerm bt
  LEFT JOIN 
      TblSynonyms s ON bt.BusinessTermID = s.BusinessTermID
  GROUP BY 
      bt.BusinessTerm, bt.BusinessTermLongDesc
  

• PostgreSQL: Use STRING_AGG() (just like SQL Server) or array_to_string(array_agg(...), ', '):

  SELECT 
      bt.BusinessTerm,
      bt.BusinessTermLongDesc,
      STRING_AGG(s.Synonym, ', ') AS Synonyms
  FROM 
      TblBusinessTerm bt
  LEFT JOIN 
      TblSynonyms s ON bt.BusinessTermID = s.BusinessTermID
  GROUP BY 
      bt.BusinessTerm, bt.BusinessTermLongDesc
  

Just make sure to adjust table/field names and join keys to match your actual database schema, and you'll have single rows for each business term with all their synonyms (and other multi-values) neatly comma-separated!

内容的提问来源于stack exchange，提问作者Jext