SQL Server 2008多字段拼接并按字母排序实现方案咨询

Hey there! Since you're stuck with SQL Server 2008 (no STRING_AGG or modern functions) and limited DBA permissions but can create tables/views in your own schema, here's a practical, step-by-step approach to concatenate up to 24 fields and sort the resulting string alphabetically.

Core Idea #

First, we need to "unpivot" your 24 columns into individual rows (one row per field value per patient). This lets us easily sort the values alphabetically before concatenating them back into a single string using the FOR XML PATH method—the go-to workaround for string aggregation in SQL Server pre-2017.

Step 1: Unpivot Columns to Rows #

We'll use UNION ALL to convert each column into a row, handling NULL values and type conversions to ensure all values are strings. Replace HospitalData with your table name, PatientID with your unique record identifier (like admission ID), and Col1/Col2/.../Col24 with your actual field names.

If some fields are numeric/datetime, cast them to NVARCHAR first to avoid type mismatch errors.

WITH UnpivotedFields AS (
    -- Convert each column to a row, handle NULLs and type conversion
    SELECT 
        PatientID,
        FieldValue = ISNULL(CAST(Col1 AS NVARCHAR(MAX)), '')
    FROM HospitalData
    UNION ALL
    SELECT 
        PatientID,
        FieldValue = ISNULL(CAST(Col2 AS NVARCHAR(MAX)), '')
    FROM HospitalData
    -- Repeat this block for all 24 columns
    UNION ALL
    SELECT 
        PatientID,
        FieldValue = ISNULL(CAST(Col24 AS NVARCHAR(MAX)), '')
    FROM HospitalData
)

Step 2: Sort & Concatenate Values #

Now we'll group by your unique identifier, sort the unpivoted values alphabetically, and concatenate them using FOR XML PATH. We use STUFF to remove the leading delimiter (, ) and handle XML escape characters to preserve original values (like &, <, >).

SELECT 
    PatientID,
    SortedConcatenatedString = REPLACE(REPLACE(REPLACE(
        STUFF(
            (
                SELECT ', ' + FieldValue
                FROM UnpivotedFields uf
                WHERE uf.PatientID = hd.PatientID
                AND FieldValue <> '' -- Optional: exclude empty values
                ORDER BY FieldValue ASC -- Sort alphabetically
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1, 2, '' -- Remove leading ", "
        ),
        '&amp;', '&'), '&lt;', '<'), '&gt;', '>') -- Fix XML escaped characters
FROM HospitalData hd
GROUP BY PatientID;

Step 3: Simplify with a View (Optional) #

If you'll run this query frequently, create a view to encapsulate the unpivot logic—this saves you from writing 24 UNION ALL blocks every time:

CREATE VIEW dbo.UnpivotedHospitalData
AS
SELECT 
    PatientID,
    FieldValue = ISNULL(CAST(Col1 AS NVARCHAR(MAX)), '')
FROM HospitalData
UNION ALL
SELECT 
    PatientID,
    FieldValue = ISNULL(CAST(Col2 AS NVARCHAR(MAX)), '')
FROM HospitalData
-- Repeat for all 24 columns
UNION ALL
SELECT 
    PatientID,
    FieldValue = ISNULL(CAST(Col24 AS NVARCHAR(MAX)), '')
FROM HospitalData;

Then your query becomes much cleaner:

SELECT 
    PatientID,
    SortedConcatenatedString = REPLACE(REPLACE(REPLACE(
        STUFF(
            (
                SELECT ', ' + FieldValue
                FROM dbo.UnpivotedHospitalData uf
                WHERE uf.PatientID = hd.PatientID
                AND FieldValue <> ''
                ORDER BY FieldValue ASC
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1, 2, ''
        ),
        '&amp;', '&'), '&lt;', '<'), '&gt;', '>')
FROM HospitalData hd
GROUP BY PatientID;

Key Notes #

• NULL Handling: The ISNULL(CAST(...), '') ensures NULL values don't break the union or show up as NULL in your final string. Remove the AND FieldValue <> '' if you want to include empty strings in the sorted result.
• Data Types: Always cast non-string fields to NVARCHAR(MAX) (or appropriate length) to avoid type conflicts in the UNION ALL.
• Special Characters: The REPLACE functions fix XML escaping for common special characters—add more if your data includes others like " for quotes.

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