Tournant Séparés par des Virgules, chaîne en lignes individuelles
J'ai une Table SQL comme ceci:
| SomeID | OtherID | Data
+----------------+-------------+-------------------
| abcdef-..... | cdef123-... | 18,20,22
| abcdef-..... | 4554a24-... | 17,19
| 987654-..... | 12324a2-... | 13,19,20
est-il une requête où je peux effectuer une requête comme SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......'
qui retourne des lignes individuelles, comme ceci:
| OtherID | SplitData
+-------------+-------------------
| cdef123-... | 18
| cdef123-... | 20
| cdef123-... | 22
| 4554a24-... | 17
| 4554a24-... | 19
Fondamentalement divisé mes données à la virgule en lignes individuelles?
Je suis conscient que le stockage d'un comma-separated
chaîne de caractères dans une base de données relationnelle semble idiot, mais l'utilisation normale de cas dans l'application du consommateur fait que vraiment utile.
Je ne veux pas faire la distinction dans l'application que j'ai besoin de pagination, donc j'ai voulu explorer les options avant de refactoring l'ensemble de l'application.
C'est SQL Server 2008
(non-R2).
- Voir aussi: periscopedata.com/blog/...
Vous devez vous connecter pour publier un commentaire.
Vous pouvez utiliser le merveilleux fonctions récursives à partir de SQL Server:
Exemple de table:
La requête
Sortie
Data
devarchar(max)
àvarchar(4000)
, par exemplecreate table Testdata(SomeID int, OtherID int, Data varchar(4000))
?OPTION (maxrecursion 0)
Enfin, l'attente est terminée avec SQL Server 2016. Ils ont introduit le Split fonction de chaîne,
STRING_SPLIT
:Toutes les autres méthodes pour diviser la chaîne XML, Cadre de tableau, tandis que la boucle, etc.. ont été époustouflé par cette
STRING_SPLIT
fonction.Voici un excellent article avec comparaison des performances: La Performance des Surprises et des Hypothèses: STRING_SPLIT.
Pour les versions plus anciennes, à l'aide de tally table voici un split fonction de chaîne(la meilleure approche possible)
Visée de Tally OH! Une Amélioration de SQL 8K “CSV Séparateur” de la Fonction
value
, pasSplitData
.Cochez cette
CROSS APPLY
, ce qui est assez utile!select t.OtherID, x.* from testData t cross apply (select item as Data from dbo.Split(t.Data,',') ) x
Février 2016 - voir le Tableau de POINTAGE Exemple - très probablement à surpasser ma TABLE ci-dessous, à partir de Février 2014. En gardant original post ci-dessous pour la postérité:
Trop de code à répétition, à mon goût, dans les exemples ci-dessus. Et je n'aime pas les performances des expressions de table communes et XML. Aussi, explicite
Id
de sorte que les consommateurs qui sont de l'ordre peut spécifier unORDER BY
clause.Agréable de voir qu'il a été résolu en 2016 version, mais pour tous ceux qui n'est pas sur que, voici deux généralisée et des versions simplifiées des méthodes ci-dessus.
Le XML méthode est courte, mais il y a bien sûr la chaîne afin de permettre le xml-trick (pas de 'mauvais' caractères.)
XML-Méthode:
Méthode récursive:
Fonction dans l'action
XML-MÉTHODE 2: Unicode Amical (Plus de courtoisie de Max Hodges)
create function dbo.splitString(@input nVarchar(max), @Splitter nVarchar(99)) returns table as
Return
SELECT Split.a.value('.', 'NVARCHAR(max)') AS Data FROM
( SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data
) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);
Veuillez consulter ci-dessous TSQL. STRING_SPLIT fonction est disponible uniquement sous le niveau de compatibilité de 130 et plus.
TSQL:
RÉSULTAT:
Couleur
rouge
bleu
vert
jaune
noir
Très tard, mais essayez ceci:
Nous étions donc avoir ceci:
tbl_Sample :
Après l'exécution de cette requête:
Merci!
STRING_SPLIT
est chouette, mais il a besoin de SQL Server 2016. docs.microsoft.com/en-us/sql/t-sql/functions/...avec seulement une infime peu de modification à la requête ci-dessus...
J'ai toujours utiliser le XML de la méthode. Assurez-vous d'utiliser XML VALIDE. J'ai deux fonctions pour convertir entre XML valide et le Texte. (J'ai tendance à dépouiller les retours à la ligne que je n'ai généralement pas besoin d'eux.
SELECT (SELECT '<&> blah' + CHAR(13)+CHAR(10) + 'next line' FOR XML PATH(''))
Fonction
Cas d'utilisation
Ou juste un select avec plusieurs jeu de résultats
Ci-dessous fonctionne sous sql server 2008
Obtiendrez tous les produit Cartésien avec l'origine des colonnes de la table plus "éléments" de split table.
Vous pouvez utiliser la fonction suivante pour extraire des données