how to reverse comma seprated values.

Question

Post reply

how to reverse comma seprated values.

rajemessage 14195

Hall of Fame

Points: 3315
More actions
November 10, 2023 at 10:32 am

#4325092

hi,
i have coma separated vales in a table. what i want to reverse it like 'CLP,CAD,GBP' pls tell me how it can be done with out using xml in efficent way.
Copy
DECLARE @TBL AS TABLE
(COLUMN1 NVARCHAR(100))
INSERT INTO @TBL
SELECT 'AUD,BRL,GBP,CAD,CLP'
SELECT COLUMN1 FROM @TBL

December 6, 2023 at 5:08 am

This was removed by the editor as SPAM

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

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

DesNorton SSC-Insane Points: 24422 More actions · Answer 1

You can find a good splitter function here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Then you can apply it as follows to split, and rebuild in reverse order

DECLARE @TBL AS table ( COLUMN1 nvarchar(100));

INSERT INTO @TBL (COLUMN1)
VALUES ( 'AUD,BRL,GBP,CAD,CLP' )
     , ( '123,ABC,ZZZ,AAA,XYZ' );

SELECT      src.COLUMN1
          , revCOLUMN1 = STUFF((SELECT ',' + splt.Item
                                FROM dbo.DelimitedSplit8K( src.COLUMN1, ',' ) AS splt
                                ORDER BY splt.ItemNumber DESC
                                FOR XML PATH('')
                                ), 1, 1, '')
FROM        @TBL AS src;

How to post data/code on a forum to get the best help.

Phil Parkin SSC Guru Points: 246991 More actions · Answer 2

Another version:

SELECT t.COLUMN1
      ,STRING_AGG (s.Item, ',') WITHIN GROUP(ORDER BY s.ItemNumber DESC)
FROM @TBL t
    CROSS APPLY
(SELECT * FROM dbo.DelimitedSplit8K (t.COLUMN1, ',') dsk ) s
GROUP BY t.COLUMN1;

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

rajemessage 14195 wrote:

hi,
i have coma separated vales in a table. what i want to reverse it like 'CLP,CAD,GBP' pls tell me how it can be done WITH OUT USING XML IN EFFICENT WAY.
Copy DECLARE @TBL AS TABLE (COLUMN1 NVARCHAR(100))
INSERT INTO @TBL SELECT 'AUD,BRL,GBP,CAD,CLP'
SELECT COLUMN1 FROM @TBL

Details in the comments...

--===== Create the test table variable (Thank you DesNorton)
DECLARE @TBL AS TABLE  (COLUMN1 nvarchar(100));
INSERT INTO @TBL (COLUMN1)
VALUES ( 'AUD,BRL,GBP,CAD,CLP' )
     , ( '123,ABC,ZZZ,AAA,XYZ' )
;
--===== Solve the problem for SQL Server 2019, WITH NO XML.
   WITH cteEnumerateRows AS
(
 SELECT  RowNum = ROW_NUMBER() OVER (ORDER BY COLUMN1) --To guarantee unique rows
        ,COLUMN1
   FROM @TBL
)
 SELECT  COLUMN1_Original = MAX(COLUMN1) --MAX() so that we don't need to GROUP BY COLUMN1
        ,ReversedCsv = STRING_AGG(s.Item, ',') WITHIN GROUP (ORDER BY s.ItemNumber DESC )
   FROM cteEnumerateRows r
  CROSS APPLY dbo.DelimitedSplit8k(COLUMN1,',') s
  GROUP BY RowNum
;

Results:

Again, get the DelimitedSplit8k function from the "Resources" link near the bottom of that article posted at the following URL.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

There is a faster mod (Thanks to LAG and Eirikur Eiriksson) in "Part 1" of the following article.

https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2

EDIT: Dang it. I opened this thread and let it sit for a bit and then started working on it without hitting the refresh button and missed Phil's post. The only difference in mine is that I don't assume that COLUMN1 doesn't have dupes in it.

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

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

Thanks, Jeff. I made the assumption that the OP did not have a table 'TBL' containing a single column 'COLUMN1' and therefore that the addition of a suitable PK would be done as part of integrating the solution into actual code. Nice tweak, nonetheless!

And, even in the US, 'Resources' is spelled the English way 🙂

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

Phil Parkin wrote:

Thanks, Jeff. I made the assumption that the OP did not have a table 'TBL' containing a single column 'COLUMN1' and therefore that the addition of a suitable PK would be done as part of integrating the solution into actual code. Nice tweak, nonetheless!
And, even in the US, 'Resources' is spelled the English way 🙂

You'll have to forgive me on the spelling error (which I've corrected). I'm not legally alive until after my first cup of coffee, especially since I went to bed a 3 this morning, couldn't sleep, and got up at about 5:30. Tough night. 😀

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