Discussion :

[fanatics]

Bonjour, je suis un peu bloqué là, je voudrais éclater à partir d'un seul select plusieurs lignes dépendamment d'un caractère. dans mon exemple c'est le '|'.
par exemple dans mon select j'ai:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
num_client            nom
   1                 |Jean|Paul|Gauthier|

je voudrais d'un select ou d'une requête qui me permet d'avoir à partir de cette ligne le résultat ce dessous:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
num_client            nom
   1                     Jean
   1                     Paul
   1                    Gauthier

Merci pour votre aide

[hmira]

1 - Fonction simple de type table, permettant de découper une chaine

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE FUNCTION dbo.FC_SPLIT_without_CTE(
        @pi_ListStr VARCHAR(8000),
        @pi_Delimiter CHAR(1) = ','
)
RETURNS @List TABLE (Item VARCHAR(8000))
BEGIN
    DECLARE @sItem VARCHAR(8000);     
    DECLARE @Pos  SMALLINT;        
    SET @Pos = CHARINDEX(@pi_Delimiter, @pi_ListStr, 0); 
    WHILE @Pos > 0
    BEGIN
        SET @sItem = RTRIM(LTRIM(SUBSTRING(@pi_ListStr, 1, @Pos - 1)));
        SET @pi_ListStr = RTRIM(LTRIM(SUBSTRING(@pi_ListStr, @Pos + 1, LEN(@pi_ListStr))));
        IF LEN(@sItem) > 0
            INSERT INTO @List (Item) VALUES (@sItem);
          SET @Pos = CHARINDEX(@pi_Delimiter, @pi_ListStr, 0); 
    END;
    IF LEN(@pi_ListStr) > 0
        INSERT INTO @List (Item) VALUES (@pi_ListStr);
    RETURN;
END; 
GO

2 - Utilisation de l'opérateur CROSS APPLY
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
num_client	nom
1	|Jean|Paul|Gauthier|
2	|Jean|Jacques|Goldman|

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT  C.num_client , L.item 
	FROM dbo.client C 
    CROSS APPLY  dbo.FC_SPLIT_without_CTE(c.nom, '|' )  L

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
num_client	item
1	Jean
1	Paul
1	Gauthier
2	Jean
2	Jacques
2	Goldman

A+

[elsuket]

Bonjour,

Les fonctions de table à instructions multiples sont contre-performantes par nature, puisqu'elles sont exécutées autant de fois qu'il y a de lignes dans le résultat.
On peut, pour dépasser ce petit problème, s'en remettre directement à XQuery :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT		X.num_client
		, N.v.value('.', 'varchar(32)') AS value
FROM		(
			SELECT	num_client
				, (CAST('<v>' + REPLACE(RIGHT(LEFT(nom, LEN(nom) - 1), LEN(nom) - 2), '|', '</v><v>') + '</v>' AS xml))
			FROM	dbo.client
		) AS X(num_client, v)
CROSS APPLY	X.v.nodes('/v') AS N(v)

Ou si vous êtes plus à l'aise avec les fonctions, alors utilisons une fonction de table en ligne (le seul type de fonction traité de façon ensembliste) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION ufn_parse_piped_string
	(@_s varchar(1024))
RETURNS TABLE
AS
RETURN
(
	SELECT		N.v.value('.', 'varchar(32)') AS value
	FROM		(VALUES (CAST('<v>' + REPLACE(RIGHT(LEFT(@_s, LEN(@_s) - 1), LEN(@_s) - 2), '|', '</v><v>') + '</v>' AS xml))) AS X(v)
	CROSS APPLY	X.v.nodes('/v') AS N(v)
)

Et un exemple d'appel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT		C.num_client
		, CN.value AS client_noms
FROM		dbo.client AS C
CROSS APPLY	dbo.ufn_parse_piped_string(C.nom) AS CN

Ce comportement et son inverse sont décrits en détail ici

@++

[hmira]

OK et Merci elsuket pour ces astuces.
A+

[fanatics]

Merci pour vos réponses, ca me permet d'avancer beaucoup plus
j'ai opté pour la solution d'elsuket, je l'ai un peu modifié à mes besoins (coté performance j'attends vos réactions , je traite un peu plus de 150000 LIGNES )
voila mon cas, je parcoure les num_client, je fais le traitement puis j’insère dans une table temporaire, qui va me servir de table de liaison avec une autre table.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
create table ##temp_table (num_client int, nom varchar(255))
 
DECLARE @num_client int
DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT DISTINCT num_client 
FROM [dbo].[client]
 
OPEN MY_CURSOR
FETCH NEXT FROM  MY_CURSOR INTO @num_client
WHILE @@FETCH_STATUS = 0 
BEGIN 
 
 insert into ##temp_table
SELECT		X.[num_client]
		, N.v.value('.', 'varchar(100)') AS value
 
FROM		(
			SELECT	[num_client]
				, (CAST('<v>' + REPLACE(RIGHT(LEFT([nom ], LEN([nom ]) - 1), LEN([nom ]) - 2), '|', '</v><v>') + '</v>' AS xml))
			FROM	[dbo].[client]
			where num_client = @num_client
		) AS X([num_client], v)
CROSS APPLY	X.v.nodes('/v') AS N(v)
 
 
    FETCH NEXT FROM MY_CURSOR INTO @num_client
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR