Script split d'une valeur en plusieurs colonnes

Version imprimable

1 pièce(s) jointe(s)

Bonjour

Je rencontre quelques difficultés dans la réalisation d'un script en SQL pour faire split d'une colonne en plusieurs autres colonnes.

J'ai une liste d'ID sous forme de string de type :

Code:

SELECT [codeT] FROM dbo.[LIST_CODE_TH]

[codeT]

Citation:

01.03
01.03.01
01.03.01.17
20.01.01.01
20.01.01.01.02
20.01.01.01.03
20.01.01.01.04
300.900.01.01.28
300.900.01.01.29
...

Je cherche à split de cette manière
Pièce jointe 591735
pour : 100.200.30.40.50

TH_CURRENT_PATH => 100.200.30.40.50
TH_PARENT_PATH => 100.200.30.40
TH_CURRENT_ID => 50
TH_PARENT_ID => 40
TH_MASTER_ID => 100
TH_MASTER_CHILD_ID => 200

J'ai donc produit cela :
Code:

1 2 3 4 5 6 7 8 SELECT [codeT] as TH_CURRENT_PATH ,PARSENAME([codeT], 1) as TH_CURRENT_ID ,PARSENAME([codeT], 2) as TH_PARENT_ID ,SUBSTRING([codeT],1,((LEN([codeT]))-(LEN(PARSENAME([codeT], 1)) + (IIF(SUBSTRING(SUBSTRING([codeT],1,((LEN([codeT]))-(LEN(PARSENAME([codeT], 1))))),((LEN(SUBSTRING([codeT],1,((LEN([codeT]))-(LEN(PARSENAME([codeT], 1))))))) ),(LEN(SUBSTRING([codeT],1,((LEN([codeT]))-(LEN(PARSENAME([codeT], 1)))))))) = '.', 1, 0))))) as TH_PARENT_PATH , REVERSE(PARSENAME(REVERSE([codeT]), 1)) as TH_MASTER_ID , REVERSE(PARSENAME(REVERSE([codeT]), 2)) AS TH_MASTER_CHILD_ID FROM dbo.[LIST_CODE_TH] ORDER BY [codeT] ASC;
avec PARSENAME j'arrive à atteindre les éléments jusqu'au 4ème niveau,
mais je n'arrive pas à accéder aux valeurs si il y'a 5 niveau ou +

merci pour vos retours.

Code:

1
2
3
4
5
6
7
8
9
10
; WITH SR as (
	SELECT 1 AS ID, '123.456.714.459' AS IP 
	UNION ALL SELECT 2, '1.2.3.4'
	UNION ALL SELECT 3, '654.641.100.001.124.14545.4165'
) , SR1 AS ( SELECT id, CAST('<c>' + REPLACE(ip, '.','</c><c>') + '</c>' AS XML) AS texte_xml FROM SR
) , SR2 AS ( SELECT id, T.C.value('(./text())[1]','NVARCHAR(25)') AS PartIp , ROW_NUMBER() OVER(PARTITION BY id ORDER BY CASE WHEN id=0 THEN 1 END) AS Rnk
	FROM SR1 CROSS APPLY texte_xml.nodes('//c') AS T(C) )
SELECT id, [1] as Part1,[2] as Part2,[3] as Part3,[4] as Part4,[5] as Part5,[6] as Part6,[7] as Part7,[8] as Part8
FROM SR2 PIVOT (MAX(PartIp) FOR Rnk IN ( [1],[2],[3],[4],[5],[6],[7],[8] ) ) AS PT
ORDER BY id

Y a sûrement plus simple avec une autre fonction que Parsename.

Votre exemple fonctionne pour le découpage oui mais je pense pas avoir compris l'implémentation de votre requête :/

Code:

1
2
3
4
5
6
7
8
9
10
11
WITH SR as ( 
		SELECT [idT] AS _id, [titleT] AS _title, [codeT] AS _str FROM [LIST_CODE_TH] 
) ,
SR1 AS ( SELECT _id, CAST('<c>' + REPLACE(_str, '.','</c><c>') + '</c>' AS XML) AS texte_xml FROM SR),
SR2 AS ( 
		SELECT _id, T.C.value('(./text())[1]','NVARCHAR(25)') AS PartStr , 
		ROW_NUMBER() OVER(PARTITION BY _id ORDER BY CASE WHEN _id=0 THEN 1 END) AS Rnk FROM SR1 CROSS APPLY texte_xml.nodes('//c') AS T(C) 
	)
SELECT _id, _title, [1] as Part1,[2] as Part2,[3] as Part3,[4] as Part4,[5] as Part5,[6] as Part6,[7] as Part7,[8] as Part8,[9] as Part9,[10] as Part10
FROM SR2 PIVOT (MAX(PartStr) FOR Rnk IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] ) ) AS PT
ORDER BY _id;

Citation:

Nom d'objet 'LIST_CODE_TH' non valide.

de plus j'ai besoin d'identifier clairement "TH_CURRENT_ID" et "TH_PARENT_ID" hors la je ne peux pas identifier dans quel colonne "Part[i]" va se trouver l'information ?

26/02/2021, 15h44
Invité

Il te manque le title dans les sous requêtes SR1 et SR2...
Pour tes conditions, ça se gère avec des CASE WHEN ... THEN ... ELSE ... END une fois le split effectué.

En tout cas, j'espère que ce n'est pas vous qui avez modélisé ce sac de nœuds, parce que ça ne mérite pas de félicitation. :roll:

1 pièce(s) jointe(s)

Encore merci de ton aide;
Non ce n'est pas de moi, je boss avec une API déjà existante et je ne peux malheureusement pas m'en séparer :/

J'ai encore un dernier petit soucie avec les digits inférieur à 10 ... il ne me retourne pas le "0"
Pièce jointe 592216
J'ai testé plusieurs méthodologie comme 2 présentes dans l'exemple ci-dessous; mais a chaque fois il me retourne une valeur de type Integer sans le "0"
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 WITH SR as (SELECT [idT] AS _id, [titleT] AS _title, RTRIM(LTRIM([codeT])) AS _str FROM [LIST_CODE_TH]) , SR1 AS ( ... ), SR2 AS ( ... ) SELECT ... ,CASE WHEN [1]<>'' THEN CAST([1] AS varchar(3)) ELSE 0 END as TH_MASTER_ID ,CASE WHEN [2]<>'' THEN CASE WHEN [2]<10 THEN CONCAT('0', [2]) ELSE [2] END ELSE 0 END as TH_MASTER_CHILD_ID FROM SR2 ...

01/03/2021, 20h37
Invité

Vous provoquez un transtypage implicite avec votre else 0 : le 0 est considéré comme un entier (integer) et du coup tous les résultats de cette colonne sont reconverti en entier !
C'est pareil pour ton autre colonne ;)
À remplacer par :

Code:

,CASE WHEN [1]<>'' THEN CAST([1] AS varchar(3)) ELSE CAST(0 AS VARCHAR(3)) END as TH_MASTER_ID

ou

Code:

,CASE WHEN [1]<>'' THEN CAST([1] AS varchar(3)) ELSE '0' END as TH_MASTER_ID

C'est effectivement ce que j'étais entrain de tester, Mdr.

Merci beaucoup.

Si sa peut aider voici mon script final :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
WITH SR as ( SELECT [idT] AS _id, [titleT] AS _title, [codeT] AS _str FROM [LIST_CODE_TH]) ,
SR1 AS ( SELECT _id,_title,_str, CAST('<c>' + REPLACE(_str, '.','</c><c>') + '</c>' AS XML) AS texte_xml FROM SR),
SR2 AS ( 
		SELECT _id,_title,_str, T.C.value('(./text())[1]','NVARCHAR(25)') AS PartStr , 
		ROW_NUMBER() OVER(PARTITION BY _id ORDER BY CASE WHEN _id=0 THEN 1 END) AS Rnk FROM SR1 CROSS APPLY texte_xml.nodes('//c') AS T(C) 
	)
SELECT _id, _title, [1] as Part1,[2] as Part2,[3] as Part3,[4] as Part4,[5] as Part5,[6] as Part6,[7] as Part7,[8] as Part8,[9] as Part9,[10] as Part10
,_str as TH_CURRENT_PATH
,SUBSTRING(_str, 1, 
CASE 
	WHEN (LEN(_str) - (LEN(ISNULL([10],ISNULL([9],ISNULL([8],ISNULL([7],ISNULL([6],ISNULL([5],ISNULL([4],ISNULL([3],ISNULL([2],ISNULL([1], 0))))))))))) +1)) > 0 THEN (LEN(_str) - (LEN(ISNULL([10],ISNULL([9],ISNULL([8],ISNULL([7],ISNULL([6],ISNULL([5],ISNULL([4],ISNULL([3],ISNULL([2],ISNULL([1], 0))))))))))) +1))
	ELSE LEN(_str)
END
) AS TH_PARENT_PATH 
,CASE 
     WHEN (([10]<>'') AND ([10] IS NOT NULL))THEN [10]
	 WHEN (([9]<>'') AND (([10]='') OR ([10] IS NULL)) )THEN [9]
	 WHEN (([8]<>'') AND (([9]='') OR ([9] IS NULL)) )THEN [8]
	 WHEN (([7]<>'') AND (([8]='') OR ([8] IS NULL)) )THEN [7]
	 WHEN (([6]<>'') AND (([7]='') OR ([7] IS NULL)) )THEN [6]
	 WHEN (([5]<>'') AND (([6]='') OR ([6] IS NULL)) )THEN [5]
	 WHEN (([4]<>'') AND (([5]='') OR ([5] IS NULL)) )THEN [4]
	 WHEN (([3]<>'') AND (([4]='') OR ([4] IS NULL)) )THEN [3]
	 WHEN (([2]<>'') AND (([3]='') OR ([3] IS NULL)) )THEN [2]
	 WHEN (([1]<>'') AND (([2]='') OR ([2] IS NULL)) )THEN [1]
     ELSE CAST(0 AS varchar(3))
END AS TH_CURRENT_ID
,CASE 
     WHEN (([10]<>'') AND ([10] IS NOT NULL))THEN [9]
     WHEN (([9]<>'') AND ([9] IS NOT NULL))THEN [8]
     WHEN (([8]<>'') AND ([8] IS NOT NULL))THEN [7]
     WHEN (([7]<>'') AND ([7] IS NOT NULL))THEN [6]
     WHEN (([6]<>'') AND ([6] IS NOT NULL))THEN [5]
     WHEN (([5]<>'') AND ([5] IS NOT NULL))THEN [4]
     WHEN (([4]<>'') AND ([4] IS NOT NULL))THEN [3]
     WHEN (([3]<>'') AND ([3] IS NOT NULL))THEN [2]
     WHEN (([2]<>'') AND ([2] IS NOT NULL))THEN [1]
     WHEN (([1]<>'') AND ([1] IS NOT NULL))THEN [1]
     ELSE CAST(0 AS varchar(3))
END AS TH_PARENT_ID
,CASE 
     WHEN [1]<>'' THEN CAST([1] AS varchar(3))
     ELSE CAST(0 AS varchar(3))
END as TH_MASTER_ID
,CASE 
     WHEN [2]<>'' THEN CAST([2] AS varchar(3))
     ELSE CAST(0 AS varchar(3))
END as TH_MASTER_CHILD_ID
 
FROM SR2 PIVOT (MAX(PartStr) FOR Rnk IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] ) ) AS PT