Sort already comma eparted value

Question

Post reply

Sort already comma eparted value

PSB

SSChampion

Points: 13195

June 8, 2020 at 7:49 pm

#3761442

Hi,

Please help me sort a comma separated column in the database table .

Create Table #CommaSeparatedString

(
 ID INT,
 Title nvarchar(10),
 StringValue nvarchar(500)
)

INSERT INTO #CommaSeparatedString (ID,Title,StringValue)

SELECT 1,'Test1','EUR HQ,EME HQ,KSA HQ,NME HQ'
UNION ALL
SELECT 2,'Test2','LAN HQ,Colombia,Peru'
UNION ALL
SELECT 3,'Test3','NAO HQ,Trinidad and Tobago,Bermuda,Guyana,Suriname'

SELECT * FROM #CommaSeparatedString

-- Desired Result
SELECT 1 AS INT,'Test1' AS Title,'EME HQ,EUR HQ,KSA HQ,NME HQ'
UNION ALL
SELECT 2 AS INT,'Test2' AS Title,'Colombia,LAN HQ,Peru'
UNION ALL
SELECT 3 AS INT,'Test3' AS Title,'Bermuda,Guyana,NAO HQ,Suriname,Trinidad and Tobago'

This topic was modified 4 years, 6 months ago by PSB.
This topic was modified 4 years, 6 months ago by PSB.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Ummm... why does KSA come first in #1 results?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PSB SSChampion Points: 13195 More actions · Answer 2

Sorry, it was my mistake . Should be 'EME HQ,EUR HQ,KSA HQNME HQ'

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Possibly something like this:

SELECT *
FROM #CommaSeparatedString css;

WITH ordered
AS (SELECT css.ID
          ,NewValue = STRING_AGG(ss.value, ',') WITHIN GROUP(ORDER BY ss.value)
    FROM #CommaSeparatedString css
        CROSS APPLY STRING_SPLIT(css.StringValue, ',') ss
    GROUP BY css.ID)
UPDATE css
SET css.StringValue = o.NewValue
FROM #CommaSeparatedString css
    JOIN ordered o
        ON o.ID = css.ID;

SELECT *
FROM #CommaSeparatedString css;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Oh dear, you have 2016 – STRING_AGG() does not work there, apologies.

Note also that your original code does not run – the column widths are not of sufficient size.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 5

I just corrected the data size. Thanks for pointing that out.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 6

Since you are on 2016 and you don't have access to STRING_AGG, you can still use FOR XML.

 Select Distinct
        ss.ID
      , ss.Title
      , SortedString = stuff((Select ',' + f.SortedValue
                               From (Select SortedValue = s.value
                                       From #CommaSeparatedString css
                                      Cross Apply string_split(css.StringValue, ',') s
                                      Where css.ID = ss.ID
                                    ) As f
                              Order By f.SortedValue
                                For xml Path(''), Type).value('.', 'nvarchar(500)'), 1, 1, '')
   From #CommaSeparatedString ss;

You may need to look at using a specific collation for the sort - depends on the collation of the data in the nvarchar column (if you don't actually use unicode data then it should be varchar). If you do actually have unicode data - but do not have special characters in the data that need to be encoded in XML you can remove the 'Type' declaration which will eliminate the XML reader in the plan.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

Is there any chance you could take the time to read a book on basic SQL and RDBMS? By definition, a table must have a key; this is not an option! Likewise, a key cannot be null. There is also no such creature as a generic "id"; it has to be the identifier of something in particular. Next, we don't like having redundant keys. So all you posted is garbage that is is completely non-relational. What you wanted to post was more like this:

CREATE TABLE CommaSeparatedStrings

(string_name VARCHAR(10) NOT NULL PRIMARY KEY,

string_value VARCHAR(500) NOT NULL);

It gets worse. Why are you using the old Sybase syntax for insertion statements? For a few decades, the ANSI/ISO standard syntax has been like this:

INSERT INTO CommaSeparatedStrings -- GARBAGE!

VALUES

('Test1', 'EUR HQ, EME HQ, KSA HQ, NME HQ'), -- not 1NF

('Test2', 'LAN HQ, Colombia, Peru'),

('Test3', 'NAO HQ, Trinidad and Tobago, Bermuda, Guyana, Suriname');

A reasonably smart SQL compiler can optimize this table-building syntax because it is a single statement.

But what you've missed completely. Is the whole concept of first normal form (1NF), which is, the very foundation of RDBMS. The values in the columns of a table are atomic values, not repeated groups. This is generally covered in the first few chapters of any book on SQL or RDBMS (usually before the fifth chapter).

CREATE TABLE SomethingStrings

(string_name VARCHAR(10) NOT NULL,

string_value VARCHAR(500) NOT NULL,

PRIMARY KEY (string_name, string_value));

INSERT INTO Some_Strings

VALUES

('Test1', 'EUR HQ’),

('Test1', ‘EME HQ’),

('Test1', ‘KSA HQ’),

('Test1', ‘NME HQ'),

('Test2', 'LAN HQ’),

('Test2', ‘Colombia’),

('Test2', ‘Peru'),

('Test3', 'NAO HQ'),

('Test3', ‘Trinidad and Tobago’),

('Test3', ‘Bermuda’),

('Test3', ‘Guyana'),

('Test3', ‘Suriname');

I've been teaching SQL for over 30 years. Why did you think that this was the right way to do RDBMS or write SQL?. What was your first programming language? Usually, people don't make random mistakes, but they mimic the syntax and semantics of the languages they know when they try to learn something new.

Please post DDL and follow ANSI/ISO standards when asking for help.