I have a column that contains comma separated values. I'm trying to figure out how to sort the comma separated values by a certain order. The sort order will be hard coded in the select statement since it does not exist in a table. I've tried using STRING_AGG WITHIN GROUP to sort the values but haven't had any luck. Can anyone assist with sorting the comma delimited column values in the following order?
Desired Sort Order = Admin, Promote, Terminate, Modify, Read
Sample Data:
CREATE TABLE #Employee(
id INT NOT NULL IDENTITY(1,1),
UserName VARCHAR(100) NOT NULL,
SecurityLevel VARCHAR(50)
)
INSERT INTO #Employee
VALUES
('Dbrown', 'Read, Modify, Promote, Terminate '),
('Swall', 'Read, Promote'),
('Esmith', 'Terminate, Read, Modify, Admin '),
('TWilt', 'Promote, Terminate, Admin, Modify, Read'),
('MLee', 'Modify, Admin, Read, Promote, Terminate')
SELECT
-- id,
--UserName,
STRING_AGG(LTRIM(sl.value), ', ') WITHIN GROUP (ORDER BY LTRIM(CASE WHEN sl.value = 'Admin' THEN 1
WHEN value = 'Promote' THEN 2
WHEN value = 'Terminate' THEN 3WHEN value = 'Modify' THEN 4WHEN value = 'Read' THEN 5end) asc)
FROM
#Employee e
CROSS APPLY STRING_SPLIT(e.SecurityLevel, ',') sl
GROUP BY
e.id
--Database format
idUserNameSecurityLevel
1DbrownRead, Modify, Promote, Terminate
2SwallRead, Promote
3EsmithTerminate, Read, Modify, Admin
4TWiltPromote, Terminate, Admin, Modify, Read
5MLeeModify, Admin, Read, Promote, Terminate
--Desired format
idUserNameSecurityLevel
1Dbrown Promote,Terminate, Modify, Read
2Swall Promote, Read
3Esmith Admin, Terminate, Modify, Read
4TWilt Admin, Promote, Terminate, Modify, Read
5MLee Admin, Promote, Terminate Modify, Read
your problem is that your "ltrim" is on the wrong place, and the string compare inside does not find the required matches)
following works
drop table if exists #employee
CREATE TABLE #Employee(
id INT NOT NULL IDENTITY(1,1),
UserName VARCHAR(100) NOT NULL,
SecurityLevel VARCHAR(50)
)
INSERT INTO #Employee
VALUES
('Dbrown', 'Read, Modify, Promote, Terminate '),
('Swall', 'Read, Promote'),
('Esmith', 'Terminate, Read, Modify, Admin '),
('TWilt', 'Promote, Terminate, Admin, Modify, Read'),
('MLee', 'Modify, Admin, Read, Promote, Terminate')
SELECT
-- id,
--UserName,
STRING_AGG(LTRIM(sl.value), ', ') WITHIN GROUP (ORDER BY LTRIM(CASE WHEN sl.value = 'Admin' THEN 1
WHEN sl.value = 'Promote' THEN 2
WHEN sl.value = 'Terminate' THEN 3 WHEN sl.value = 'Modify' THEN 4 WHEN sl.value = 'Read' THEN 5 end) asc)
FROM
#Employee e
CROSS APPLY STRING_SPLIT(e.SecurityLevel, ',') sl1
cross apply (select trim(sl1.value) as Value) sl
GROUP BY
e.id
April 25, 2024 at 10:45 pm
Thanks for the help. I understand why the results were incorrect now.
Also, this query is needed to format one column and will later be joined with a larger query by the id column. Do you know if there is a more efficient way of doing something like this?
April 25, 2024 at 10:55 pm
too many things to consider in order to say if there is a more efficient way - depends on volumes and number of times it is executed as well if the process allows for temp tables (e.g. its not within a view).
number of possible combinations of that column may also allow for something to be done earlier (on distinct combinations) and then used to join to the table without the need to do the split/order/rejoin at that stage.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply