Hi @Rajesh Kumar Yadav
enable_ordinal in STRING_SPLIT function is only available in Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (serverless SQL pool only), and SQL Server 2022 (16.x) and later versions.
If SQL Server 2022 (16.x) and later, please refer to Viorel's answer above.
If you are using version between SQL Server 2017 (14.x) and SQL Server 2022 (16.x), then try this query:
SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY [key] DESC) AS New_Column1
FROM @TBL CROSS APPLY OPENJSON(N'["' + REPLACE(COLUMN1, ',', N'","') + N'"]') AS x
If your version is before SQL Server 2017 (14.x), then you need to consider xml way.
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.