Sort comma separated value in SQL Table

Question

Post reply

Sort comma separated value in SQL Table

Viewing 8 posts - 16 through 22 (of 22 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

PSB - Tuesday, December 4, 2018 10:10 AM

Your database isn't in compatibility mode 120 or lower is it? (although, you'll need 140+ for STRING_AGG)

Thom~

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

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

PSB - Tuesday, December 4, 2018 10:10 AM

What is the compatibility level of the database you are working in?

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 3

PSB

SSChampion

Points: 13195

December 4, 2018 at 10:14 am

#2015472

. Set at 110

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

PSB - Tuesday, December 4, 2018 10:14 AM
. Set at 110

So now you understand the problem, I presume?

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

If I change it to 140, will there be any implications ?

Thanks,
PSB

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

PSB - Tuesday, December 4, 2018 10:32 AM
If I change it to 140, will there be any implications ?
Thanks,
PSB

Of course. It will be treated as a 2017 database rather than a 2012 database. The new cardinality estimator will kick in. New features and functions will be available to you. Do not do this on a production system you care about until you have performed testing.

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 7

All right, I have changed the compatibility level to 140 for my database .

I have different Line items for Country column
Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Uzbekistan, Turkmenistan
Astrakhan, Sakhalin
Azerbaijan, Kazakhstan, Turkmenistan, Uzbekistan

With this code:

;WITH Split (Value)

AS

(

SELECT DISTINCT Country

FROM [dbo].[Table] YT CROSS APPLY STRING_SPLIT(REPLACE([Country], ' ', ''), ',') ss

)

SELECT STRING_AGG(Split.Value, ', ') WITHIN GROUP (ORDER BY Split.Value)

FROM Split;

I get all values in one row :
Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Turkmenistan, Uzbekistan, Astrakhan, Sakhalin, Azerbaijan, Kazakhstan, Turkmenistan, Uzbekistan, Argentina, Bolivia, Argentina, Bolivia, Brazil, Chile, Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Turkmenistan, Uzbekistan, Azerbaijan, Brazil, California, Canada Land, Houston, Midcontinent, Northeast, Rockies, South Texas, Southeast, West Texas, Colombia, Ecuador, Colombia, Peru, Ecuador, Kazakhstan, Kazakhstan, Uzbekistan , LAN HQ, LAS HQ, Sakhalin, Turkmenistan

Thanks,
PSB

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

PSB - Tuesday, December 4, 2018 10:38 AM
All right, I have changed the compatibility level to 140 for my database .
I have different Line items for Country column
Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Uzbekistan, Turkmenistan
Astrakhan, Sakhalin
Azerbaijan, Kazakhstan, Turkmenistan, Uzbekistan
With this code:
;WITH Split (Value)
AS
(
SELECT DISTINCT Country
FROM [dbo].[Table] YT CROSS APPLY STRING_SPLIT(REPLACE([Country], ' ', ''), ',') ss
)
SELECT STRING_AGG(Split.Value, ', ') WITHIN GROUP (ORDER BY Split.Value)
FROM Split;
I get all values in one row :
Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Turkmenistan, Uzbekistan, Astrakhan, Sakhalin, Azerbaijan, Kazakhstan, Turkmenistan, Uzbekistan, Argentina, Bolivia, Argentina, Bolivia, Brazil, Chile, Astrakhan, Azerbaijan, Kazakhstan, Russia Land, Sakhalin, Turkmenistan, Uzbekistan, Azerbaijan, Brazil, California, Canada Land, Houston, Midcontinent, Northeast, Rockies, South Texas, Southeast, West Texas, Colombia, Ecuador, Colombia, Peru, Ecuador, Kazakhstan, Kazakhstan, Uzbekistan , LAN HQ, LAS HQ, Sakhalin, Turkmenistan
Thanks,
PSB

Try changing SELECT DISTINCT Country to SELECT value

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