CODEWITHSUNDEEP
stringt-sqldata-manipulation
Derrick Bakhuis
Derrick Bakhuis

Reputation: 47

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

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');

Upvotes: 0

Views: 165

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Upvotes: 1

Related Questions