How to reorder values in a row alphabetically using T-SQL?

Question

I need to reorder the values in rows of a table by alphabetical order, for example:

Id      Values
--------------------------------
1       Banana, Apple, Oranges
2       Oranges, Melon, Cucumber
3       Cucumber, Banana, Apple

The expected output should be:

Id      Values
--------------------------------
1       Apple, Banana, Oranges
2       Cucumber, Melon, Oranges
3       Apple, Banana, Cucumber

You can generate the data above using the following code:

CREATE TABLE [Table] (
  [Id] INT NOT NULL,
  [Values] VARCHAR(30) NOT NULL,
  CONSTRAINT [PK_Table_Id] PRIMARY KEY CLUSTERED ([Id])
);
GO
INSERT INTO [Table] ([Id], [Values]) VALUES (1, 'Banana, Apple, Oranges'),(2, 'Oranges, Melon, Cucumber'),(3, 'Cucumber, Banana, Apple');

What you have tried so far? Please put a [Minimal, Complete, and Verifiable](https://stackoverflow.com/help/mcve) example in the question itself. — Rajesh Pandya, Apr 24 '19 at 07:27
Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Apr 24 '19 at 07:31
I am running SQL Server 2017. @Zohar Peled, I am using a comma separated list in the rows to dynamically run stored procedures in azure data factory. The suggestion provided by Tim Biegeleisen is very useful. It remains to be seen if we will implement this. — Derrick Bakhuis, Apr 24 '19 at 09:09
Since it's so easy to create comma delimited strings from table rows in 2017 (as shown in Tim's answer), I strongly suggest you to at least consider normalizing your database. — Zohar Peled, Apr 24 '19 at 09:50

score 1 · Accepted Answer · answered Apr 24 '19 at 07:31

If you are using SQL Server 2017 or later, we can use a combination of STRING_SPLIT and STRING_AGG:

WITH cte AS (
    SELECT Id, value  
    FROM [Table]
    CROSS APPLY STRING_SPLIT([Values], ', ')
)

SELECT
    Id,
    STRING_AGG(value, ', ') WITHIN GROUP (ORDER BY value) AS [Values]
FROM cte
GROUP BY Id
ORDER BY Id;

However, I seriously suggest that you stop just with my CTE step above, because storing CSV values in your table is a bad idea from the very beginning. So, once you have each value per Id on a separate row, you should stop, because then your data is already normalized, or at least much closer to it.

Your closing paragraph should be the first thing in your answer. It alone is worth the upvote. — Zohar Peled, Apr 24 '19 at 07:38

How to reorder values in a row alphabetically using T-SQL?

1 Answers1