Search code examples
sqlsql-serversql-server-2017

How to combine return results of query in one row

I have a table that save personnel code.

When I select from this table I get 3 rows result such as:

2129,3394,3508,3534
2129,3508
4056

I want when create select result combine in one row such as:

2129,3394,3508,3534,2129,3508,4056

or distinct value such as:

2129,3394,3508,3534,4056
Solution

  • You should ideally avoid storing CSV data at all in your tables. That being said, for your first result set we can try using STRING_AGG:

    SELECT STRING_AGG(col, ',') AS output
FROM yourTable;

    Your second requirement is more tricky, and we can try going through a table to remove duplicates:

    WITH cte AS (
    SELECT DISTINCT VALUE AS col
    FROM yourTable t
    CROSS APPLY STRING_SPLIT(t.col, ',')
)

SELECT STRING_AGG(col, ',') WITHIN GROUP (ORDER BY CAST(col AS INT)) AS output
FROM cte;

    Demo