IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

MS SQL Server Discussion :

Eclatement de deux lignes


Sujet :

MS SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Inscrit en
    Mai 2010
    Messages
    52
    Détails du profil
    Informations forums :
    Inscription : Mai 2010
    Messages : 52
    Par défaut Eclatement de deux lignes
    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

  2. #2
    Membre Expert

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Billets dans le blog
    8
    Par défaut
    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+

  3. #3
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    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

    @++

  4. #4
    Membre Expert

    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Septembre 2003
    Messages
    733
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2003
    Messages : 733
    Billets dans le blog
    8
    Par défaut
    OK et Merci elsuket pour ces astuces.
    A+

  5. #5
    Membre averti
    Inscrit en
    Mai 2010
    Messages
    52
    Détails du profil
    Informations forums :
    Inscription : Mai 2010
    Messages : 52
    Par défaut
    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

Discussions similaires

  1. Réponses: 5
    Dernier message: 10/04/2006, 12h07
  2. Réponses: 9
    Dernier message: 08/11/2005, 09h55
  3. Réponses: 5
    Dernier message: 09/01/2005, 19h54
  4. [VB.NET] DataGrid : titre des colonnes sur deux lignes
    Par Lahouari dans le forum Windows Forms
    Réponses: 6
    Dernier message: 06/12/2004, 14h44
  5. Deux lignes sur un TButton
    Par bml dans le forum Composants VCL
    Réponses: 2
    Dernier message: 24/07/2003, 11h17

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo