如何在SAP HANA中对逗号分隔列的元素降序排序并整列排序？

To get both the comma-separated numeric values within each SUBS_IDS entry sorted in descending order and the entire column of processed values sorted, we need to break the problem into two clear steps: first reordering the elements inside each string, then sorting the resulting column.

Why Your Original Query Didn’t Work #

The ORDER BY at the end of your original query only sorts rows based on the raw SUBS_IDS string, and STRING_AGG alone can’t reorder existing comma-separated values—you have to split the string first to manipulate individual elements.

Step-by-Step Solution Query #

Here’s the corrected query that handles both requirements:

WITH ProcessedSubscriptions AS (
    -- Split each SUBS_IDS string into individual numeric values
    SELECT 
        dsx.document_name_id,
        dsx.document_name,
        -- Aggregate sorted values back into a comma-separated string
        STRING_AGG(CAST(split_value AS INTEGER), ',' ORDER BY CAST(split_value AS INTEGER) DESC) AS sorted_subs_ids
    FROM ZTED_GLOBAL.Y_DOCUMENT_SUBSTANCE_XREFS dsx
    -- Split the SUBS_IDS string into separate rows for each value
    CROSS JOIN UNNEST(STRING_SPLIT(dsx.subs_ids, ',')) AS split_value
    WHERE dsx.document_type_id = 1
    -- Group by other columns to recombine each original row's values
    GROUP BY dsx.document_name_id, dsx.document_name
)
-- Select distinct processed values and sort the entire column
SELECT DISTINCT 
    sorted_subs_ids AS SUBS_IDS,
    document_name_id,
    document_name
FROM ProcessedSubscriptions
-- Sort the entire column (adjust ASC/DESC based on your needs)
ORDER BY sorted_subs_ids;

How This Works #

1. Splitting the String: STRING_SPLIT breaks each SUBS_IDS entry into individual string values, which we cast to integers to ensure proper numeric sorting (string sorting would treat "170" as smaller than "22", which isn’t what we want).
2. Sorting Within Each Row: STRING_AGG combines the split values back into a string, using ORDER BY CAST(split_value AS INTEGER) DESC to arrange them in descending numeric order.
3. Sorting the Entire Column: The final ORDER BY sorted_subs_ids sorts all rows based on the processed comma-separated string. If you’d prefer to sort by the largest numeric value in each entry instead of string order, use this adjusted version:

WITH ProcessedSubscriptions AS (
    SELECT 
        dsx.document_name_id,
        dsx.document_name,
        STRING_AGG(CAST(split_value AS INTEGER), ',' ORDER BY CAST(split_value AS INTEGER) DESC) AS sorted_subs_ids,
        MAX(CAST(split_value AS INTEGER)) AS max_subs_id -- Calculate the largest value for sorting
    FROM ZTED_GLOBAL.Y_DOCUMENT_SUBSTANCE_XREFS dsx
    CROSS JOIN UNNEST(STRING_SPLIT(dsx.subs_ids, ',')) AS split_value
    WHERE dsx.document_type_id = 1
    GROUP BY dsx.document_name_id, dsx.document_name
)
SELECT DISTINCT 
    sorted_subs_ids AS SUBS_IDS,
    document_name_id,
    document_name
FROM ProcessedSubscriptions
ORDER BY max_subs_id DESC; -- Sort by the largest numeric value in each entry

Testing with Your Sample Data #

For your input values:

• 22,1 → remains 22,1 (already in descending order)
• 3,22,1 → becomes 22,3,1
• 170,180 → becomes 180,170

The final ORDER BY will sort these processed entries according to your chosen criteria.

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