J'ai le tableau suivant:
Fruits Normalized_Fruits banana,apple apple,banana apple,banana apple,banana kiwi,jackfruit jackfruit, kiwi jackfruit, kiwi jackfruit, kiwi
5 Réponses :
Donnez-lui un coup de feu ... Je vais probablement être cloué par des personnes non curseurs, mais c'est ce que je suis venu avec.
Une de mes plus grandes plaintes concernant string_split est qu'il manque la position ordinale de chaque valeur. Cela facilite beaucoup les situations de celui-ci. Voici une autre approche à ce sujet. J'utilise le séparateur de Jeff Moden, qui peut être trouvé ici . Il n'y a vraiment pas besoin d'un curseur ici.
J'ai également pris la liberté d'ajouter une colonne groupid afin que vous sachiez quelle ligne chaque valeur appartient à une fois que vous l'analysez. Si la colonne de fruits est unique, vous pouvez ignorer cela mais difficile à raconter à coup sûr.
Grande solution! Commencez à réaliser la difficulté d'atteindre des solutions élégantes lorsque la manière dont les données sont stockées viole les principes SQL de manière flagrante.
Dang It Sean ... :-) Je continue à oublier Row_Number () sur la partition . Bon travail! +1
Si vous utilisez SQL 2017 ou plus:
IF OBJECT_ID('tempdb..#Fruits') IS NOT NULL DROP TABLE #Fruits;
CREATE TABLE #Fruits(Fruits VARCHAR(100));
INSERT INTO #Fruits (Fruits) VALUES
('banana,apple'),
('apple,banana'),
('kiwi,jackfruit'),
('jackfruit, kiwi')
;
;WITH Split AS (
SELECT DISTINCT a.Fruits,RTRIM(LTRIM(tbl.col.value ('@Value', 'nvarchar(max)'))) AS [Fruit]
FROM (SELECT f.Fruits,CONVERT(XML,'<N Value="' + REPLACE(f.Fruits,',','"></N><N Value="') + '"></N>') AS [x] FROM #Fruits f) a
CROSS APPLY a.x.nodes('//N') AS tbl (col)
)
SELECT r.Fruits,STUFF((SELECT ',' + s.Fruit FROM Split s WHERE s.Fruits = r.Fruits ORDER BY s.Fruit FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS [NormalizedFruits]
FROM #Fruits r
;
IF OBJECT_ID('tempdb..#Fruits') IS NOT NULL DROP TABLE #Fruits;
Puisque nous ne parlons que d'une liste délimitée de virgule de deux chaînes purement alphabétiques, je vais simplement jeter un pain de pain de pain pour le plaisir et la concision. Les garnitures sont là en raison de l'utilisation incohérente des espaces dans les données source et du sinon code> pourraient être plus courtes, mais je voulais que les résultats soient cohérents. SELECT
Fruits
,CASE
WHEN LTRIM(RTRIM(PARSENAME(REPLACE(Fruits,',','.'),2))) > LTRIM(RTRIM(PARSENAME(REPLACE(Fruits,',','.'),1)))
THEN LTRIM(RTRIM(PARSENAME(REPLACE(Fruits,',','.'),1))) + ', ' + LTRIM(RTRIM(PARSENAME(REPLACE(Fruits,',','.'),2)))
ELSE LTRIM(RTRIM(PARSENAME(REPLACE(Fruits,',','.'),2))) + ', ' + LTRIM(RTRIM(PARSENAME(REPLACE(Fruits,',','.'),1)))
END AS Normalized_Fruits
FROM #Fruits
et - juste pour le plaisir - une solution de plus appelant - Ceci est la requête p> xquery code> à la rescousse. SELECT f.*
,CAST('<x>' + REPLACE(REPLACE(f.Fruits,' ',''),',','</x><x>') + '</x>' AS XML)
.query('
for $f in /x/text()
order by $f
return <y>{concat(",",$f)}</y>
')
.value('substring(.,2,1000)','nvarchar(max)')
FROM @Fruits f;
Corrigez votre modèle de données! Ne stockez pas de listes de choses dans une chaîne délimitée.
Vous prenez des difficultés ici parce que vous stockez des données délimitées. Cela viole 1nf et n'est rien d'autre que douloureux. Vous pouvez normaliser vos données et arrêter de stocker des informations délimitées comme celle-ci? Sinon, vous devez d'abord diviser vos chaînes sur virgule, puis commandez ces chaînes et finalement les réassembler.
@Gordonlinoff Malheureusement, je n'ai aucun contrôle sur leur façon dont les données sont stockées.
@Seanlange Malheureusement, je n'ai aucun contrôle sur leur façon dont les données sont stockées. J'essaie d'accomplir l'étape 2 et 3 ("commander ces chaînes et enfin les réassembler")
On dirait que les devoirs ...
Utilisez la fonction string_split (SQL 2016+) pour les table et les rejoindre correctement avec une virgule entre elles.
@Wei_dba merci wei. Voulez-vous envisager de poster vos pensées comme une réponse?
ASIDES: Vous voudrez peut-être envisager normalisant pendant que vous êtes "normalisant", par exemple. Ajouter des tables pour mots et occurrences de mot . Cela devrait faire des requêtes comme Ugli et coing et non kumquat plutôt plus pratique. C'est aussi une bonne pratique d'inclure une question , de préférence spécifique, lors de la publication d'une "question".
@Habo je vais essayer de rendre la question plus précise. Merci.