Sort comma separated value in SQL Table

• PSB

  SSChampion

  Points: 13195

  More actions

  February 1, 2018 at 9:48 am

  #326890

  Hi

   I want to sort a column value alphabetically which is already in a sql table to something like below :

  Tamesha Rios, Damian Richard, Tim David     --> to Damien Richard, Tamesha Rios, Tim David

  Thanks,
  PSB

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 1, 2018 at 10:02 am

  #1978225

  As you're using SQL Server 2016 and ordinal position doesn't matter, have a look at STRING_SPLIT() (Transact-SQL). Then, have a look at how to create a delimited string using T-SQL; there are literally 1,000's of answer out there if you use Google. Post what you've tried if you get stuck, and we'll be happy to nudge you in the right direction.

  Ideally, however, don't store your values as a delimited string in SQL Server; store each item in it's own row.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  February 1, 2018 at 5:00 pm

  #1978284

  The question now is, especially since you posted in a 2016 forum, are you actually using SQL Server 2016?

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

• Thom A

  SSC Guru

  Points: 99126

  More actions

  February 2, 2018 at 1:36 am

  #1978310

  Jeff Moden - Thursday, February 1, 2018 5:00 PM

  The question now is, especially since you posted in a 2016 forum, are you actually using SQL Server 2016?

  One would hope.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• subramaniam.chandrasekar

  SSCarpal Tunnel

  Points: 4223

  More actions

  February 6, 2018 at 12:34 am

  #1978659

  PSB - Thursday, February 1, 2018 9:48 AM

  Hi

   I want to sort a column value alphabetically which is already in a sql table to something like below :

  Tamesha Rios, Damian Richard, Tim David     --> to Damien Richard, Tamesha Rios, Tim David

  Thanks,
  PSB

  I've created a test table and small static script which produces your desired output, 

  
SELECT *  INTO #TestTable FROM (VALUES
   ('Tamesha Rios,Damian Richard,Tim David')
   )v(CSVField);

  SELECT CSVField,  PARSENAME(REPLACE(CSVField,',','.'),2) + ',' + PARSENAME(REPLACE(CSVField,',','.'),3) + ',' + PARSENAME(REPLACE(CSVField,',','.'),1) 'Name'  FROM #TestTable


  For realtime dynamic conversion, you'd need to create an user defined function which does the same. Please keep an eye on the performance of your function as well while executing....

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  February 6, 2018 at 6:58 am

  #1978693

  subramaniam.chandrasekar - Tuesday, February 6, 2018 12:34 AM

  PSB - Thursday, February 1, 2018 9:48 AM

  Hi

   I want to sort a column value alphabetically which is already in a sql table to something like below :

  Tamesha Rios, Damian Richard, Tim David     --> to Damien Richard, Tamesha Rios, Tim David

  Thanks,
  PSB

  I've created a test table and small static script which produces your desired output, 

  
SELECT *  INTO #TestTable FROM (VALUES
   ('Tamesha Rios,Damian Richard,Tim David')
   )v(CSVField);

  SELECT CSVField,  PARSENAME(REPLACE(CSVField,',','.'),2) + ',' + PARSENAME(REPLACE(CSVField,',','.'),3) + ',' + PARSENAME(REPLACE(CSVField,',','.'),1) 'Name'  FROM #TestTable


  For realtime dynamic conversion, you'd need to create an user defined function which does the same. Please keep an eye on the performance of your function as well while executing....

  That's nice but it will fail if there are more than 4 names.

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

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  February 6, 2018 at 7:01 am

  #1978694

  @PSB,

  Where you able to solve your problem from what has been posted so far?  Even if you have, you might want to post what you've come up with because, as subramaniam.chandrasekar posted, there can be some pretty severe performance problems with these types of things and we can help.

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

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  February 6, 2018 at 8:55 am

  #1978717

  Looks like the OP has bailed on this thread but it's still an interesting request from a SQL Server versioning point of view (IMO), due to new functions that have been added in the last two releases.

  Below are 3 different possible solutions that will work, depending on what version of SQL Server you're using... 

  IF OBJECT_ID('tempdb..#Test Data', 'U') IS NOT NULL
DROP TABLE #TestData;

  CREATE TABLE #TestData (
    fk_id INT NOT NULL IDENTITY(1,1),
    FullNameCSV VARCHAR(8000) NOT NULL
    );
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');

  --================================================================================================

  --------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
    #TestData td
    CROSS APPLY (
                SELECT
                    ', ' + LTRIM(ds.Item)
                FROM
                    dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds    --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
                ORDER BY
                    LTRIM(ds.Item)
                FOR XML PATH('')
                ) fco (FullNameCSV_Ordered)
GROUP BY
    td.fk_id,
    fco.FullNameCSV_Ordered;

  --------------------------------------------------------------------------------------------------                    
-- SS 2016 & after
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
    #TestData td
    CROSS APPLY (
                SELECT
                    ', ' + LTRIM(ss.value)
                FROM
                    STRING_SPLIT(td.FullNameCSV, ',') ss    --<< STRING_SPLIT() became available in 2016
                ORDER BY
                    LTRIM(ss.value)
                FOR XML PATH('')
                ) fco (FullNameCSV_Ordered)
GROUP BY
    td.fk_id,
    fco.FullNameCSV_Ordered;

  --------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value))    --<< STRING_AGG() became available in 2017
FROM
    #TestData td
    CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
    td.fk_id;

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  February 6, 2018 at 9:57 am

  #1978735

  Jason A. Long - Tuesday, February 6, 2018 8:55 AM

  Looks like the OP has bailed on this thread but it's still an interesting request from a SQL Server versioning point of view (IMO), due to new functions that have been added in the last two releases.

  Below are 3 different possible solutions that will work, depending on what version of SQL Server you're using... 

  IF OBJECT_ID('tempdb..#Test Data', 'U') IS NOT NULL
DROP TABLE #TestData;

  CREATE TABLE #TestData (
    fk_id INT NOT NULL IDENTITY(1,1),
    FullNameCSV VARCHAR(8000) NOT NULL
    );
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');

  --================================================================================================

  --------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
    #TestData td
    CROSS APPLY (
                SELECT
                    ', ' + LTRIM(ds.Item)
                FROM
                    dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds    --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
                ORDER BY
                    LTRIM(ds.Item)
                FOR XML PATH('')
                ) fco (FullNameCSV_Ordered)
GROUP BY
    td.fk_id,
    fco.FullNameCSV_Ordered;

  --------------------------------------------------------------------------------------------------                    
-- SS 2016 & after
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
    #TestData td
    CROSS APPLY (
                SELECT
                    ', ' + LTRIM(ss.value)
                FROM
                    STRING_SPLIT(td.FullNameCSV, ',') ss    --<< STRING_SPLIT() became available in 2016
                ORDER BY
                    LTRIM(ss.value)
                FOR XML PATH('')
                ) fco (FullNameCSV_Ordered)
GROUP BY
    td.fk_id,
    fco.FullNameCSV_Ordered;

  --------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value))    --<< STRING_AGG() became available in 2017
FROM
    #TestData td
    CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
    td.fk_id;

  Very cool summary.

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

• jonathan.crawford

  SSCertifiable

  Points: 6610

  More actions

  February 6, 2018 at 10:27 am

  #1978741

  your DROP TABLE statement has an extra space, here's the corrected version, and I added in one in the 2008 & After section if you wanted to order by lastname instead of firstname (didn't modify the others because I'm stuck in 2014 at the moment) :

  IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

  CREATE TABLE #TestData (
fk_id INT NOT NULL IDENTITY(1,1),
FullNameCSV VARCHAR(8000) NOT NULL
);
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');

  --================================================================================================

  --------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ds.Item)
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
LTRIM(ds.Item)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;

--------------------------------------------------------------------------------------------------
-- SS 2008 & after - Lastname first
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
    #TestData td
    CROSS APPLY (
                SELECT
                    ', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
                FROM
                    dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds    --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
                ORDER BY
                    --LTRIM(ds.Item)
     ', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
                FOR XML PATH('')
                ) fco (FullNameCSV_Ordered)
GROUP BY
    td.fk_id,
    fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2016 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ss.value)
FROM
STRING_SPLIT(td.FullNameCSV, ',') ss --<< STRING_SPLIT() became available in 2016
ORDER BY
LTRIM(ss.value)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;

  --------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
td.fk_id,
FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value)) --<< STRING_AGG() became available in 2017
FROM
#TestData td
CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
td.fk_id;

  -------------------------------------------------------------------------------------------------------------------------------------
  Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  February 6, 2018 at 12:35 pm

  #1978759

  Jeff Moden - Tuesday, February 6, 2018 9:57 AM

  Very cool summary.

  Thank you sir! 🙂

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  February 6, 2018 at 1:25 pm

  #1978773

  jonathan.crawford - Tuesday, February 6, 2018 10:27 AM

  your DROP TABLE statement has an extra space, here's the corrected version, and I added in one in the 2008 & After section if you wanted to order by lastname instead of firstname (didn't modify the others because I'm stuck in 2014 at the moment) :

  IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

  CREATE TABLE #TestData (
fk_id INT NOT NULL IDENTITY(1,1),
FullNameCSV VARCHAR(8000) NOT NULL
);
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');

  --================================================================================================

  --------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ds.Item)
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
LTRIM(ds.Item)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;

--------------------------------------------------------------------------------------------------
-- SS 2008 & after - Lastname first
SELECT
    td.fk_id,
    FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
    #TestData td
    CROSS APPLY (
                SELECT
                    ', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
                FROM
                    dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds    --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
                ORDER BY
                    --LTRIM(ds.Item)
     ', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
                FOR XML PATH('')
                ) fco (FullNameCSV_Ordered)
GROUP BY
    td.fk_id,
    fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2016 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ss.value)
FROM
STRING_SPLIT(td.FullNameCSV, ',') ss --<< STRING_SPLIT() became available in 2016
ORDER BY
LTRIM(ss.value)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;

  --------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
td.fk_id,
FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value)) --<< STRING_AGG() became available in 2017
FROM
#TestData td
CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
td.fk_id;

  I don't see what you're talking about with the extra space thing... It's just, "DROP TABLE #TestData;", which has the exactly 1 space between words... not that it actually matters...

  As far as doing the sort by last name... You're making a dangerous, and usually faulty, assumption... IE, that each individual only has one or two names. Throw in a middle name and now your solution is sorting by middle name not last name.

  Add the fact that many people have 2 (or more) middle names and/or double surnames, trying to problematically parse, classify & sort name parts is actually an extremely complex task that isn't likely to ever be 100% correct.
  Some examples:
  Vincent Van Gogh
  Leonardo da Vinci 
  Helena Bonham Carter
  John Ronald Reuel Tolkien (JRR Tolkien)
  George Raymond Richard Martin

  The moral of the story... Unless it's an absolute business requirement, DO NOT offer to do this. If it is a requirement, made sure you get VERY explicit rules as to how the business wants you to determine the the last name... Odds are the business person making the request didn't put much thought into the complexities of the task and you don't want to be the one who gets held responsible for bad sorts in a business critical application.

  Just my 2 cents...

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  February 6, 2018 at 1:39 pm

  #1978778

  The extra space was in your IF before the DROP, not in the actual DROP itself.

  Agreed on the rest.

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

• Jason A. Long

  SSC-Insane

  Points: 23780

  More actions

  February 6, 2018 at 1:54 pm

  #1978787

  Jeff Moden - Tuesday, February 6, 2018 1:39 PM

  The extra space was in your IF before the DROP, not in the actual DROP itself.

  Agreed on the rest.

  "I see", said the blind man as he picked up his hammer and saw...

  Not sure how I let that happen... Good catch! Both of you.

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  February 6, 2018 at 3:11 pm

  #1978795

  Jason A. Long - Tuesday, February 6, 2018 1:54 PM

  Jeff Moden - Tuesday, February 6, 2018 1:39 PM

  The extra space was in your IF before the DROP, not in the actual DROP itself.

  Agreed on the rest.

  "I see", said the blind man as he picked up his hammer and saw...

  Not sure how I let that happen... Good catch! Both of you.

  Heh... in the land of the blind, the one eyed man is king. 😉

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