Comment appliquer 3 valeurs pour 1 ligne à 3 lignes avec chaque valeur ?

Question

J'ai un certain nombre de lignes qui ont de 1 à 6 valeurs. Par exemple :

Param1: A|B|C|D
Param2: B|Y
Param3: A

Je dois le transformer comme suit :

Param1: A
Param1: B
Param1: C
Param1: D
Param2: B
Param2: Y
Param3: A

Je suppose qu'un pivot-unpivot pourrait fonctionner, mais il y a beaucoup de conditions et de champs que j'ai besoin d'obtenir. J'ai également une vue qui divise toutes les valeurs et les compte. Dans l'exemple ci-dessus, il renverra un ensemble de données comme celui-ci :

A 2
B 2
C 1
D 1
Y 1

Voici mon propre exemple, qui fonctionne bien avec quelques enregistrements mais très mal avec plus de 100 000 lignes.

L'histoire initiale est la suivante. J'ai quelques objets (obj), chacun a ses paramètres (prm), qui ont leurs valeurs (val). Ainsi, comme vous le voyez, chaque objet est comme un arbre, que je dois développer. Voici une simulation :

DECLARE @x TABLE
    (
      prm INT ,
      iin VARCHAR(20) ,
      oout VARCHAR(20)
    ) 
INSERT  INTO @x
VALUES  ( 1, 'A/B/C', 'A' )
INSERT  INTO @x
VALUES  ( 1, 'A/B/C', 'B' )
INSERT  INTO @x
VALUES  ( 1, 'A/B/C', 'C' )
INSERT  INTO @x
VALUES  ( 3, 'D', 'D' )
INSERT  INTO @x
VALUES  ( 2, 'R/G', 'R' )
INSERT  INTO @x
VALUES  ( 2, 'R/G', 'G' )

DECLARE @y TABLE
    (
      obj INT ,
      prm INT ,
      val VARCHAR(20)
    ) 
INSERT  INTO @y
VALUES  ( 10, 1, 'A/B/C' )
INSERT  INTO @y
VALUES  ( 10, 2, 'R/G' )
INSERT  INTO @y
VALUES  ( 10, 3, 'D' )
INSERT  INTO @y
VALUES  ( 20, 2, 'R/G' )
INSERT  INTO @y
VALUES  ( 20, 3, 'D' )

DECLARE @z TABLE
    (
      id INT ,
      obj INT ,
      prm INT ,
      val VARCHAR(20)
    ) 
INSERT  INTO @z
VALUES  ( 1, 10, 1, NULL ) 
INSERT  INTO @z
VALUES  ( 2, 10, 1, NULL ) 
INSERT  INTO @z
VALUES  ( 3, 10, 1, NULL ) 
INSERT  INTO @z
VALUES  ( 4, 10, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 5, 10, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 6, 10, 3, NULL ) 
INSERT  INTO @z
VALUES  ( 7, 20, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 8, 20, 2, NULL ) 
INSERT  INTO @z
VALUES  ( 9, 20, 3, NULL )

Et décision :

;
WITH    a AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY prm ORDER BY prm ) n ,
                    *
           FROM     @x
         ),
    b AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY obj, prm ORDER BY obj, prm ) n ,
                    *
           FROM     @z
         )
UPDATE  b
SET     b.val = a.oout
FROM    b
        INNER JOIN @y y ON y.obj = b.obj
                           AND y.prm = b.prm
        INNER JOIN a ON a.n = b.n
                        AND a.prm = b.prm
                        AND y.val = a.iin
SELECT  *
FROM    @z

@y table - est un tableau avec des arguments comme dans le premier exemple, où Param1,Param2 est 1,2 etc. sur la colonne prm concernant un objet dans obj
@z table - est une simulation avec valeur est fixé à null, ce qui représente les paramètres qui doivent être remplis avec des valeurs.
@x tableau - est une simulation de la division des valeurs, qui doit être appliquée à l'ensemble de l'entreprise. @y en remplaçant les valeurs nulles de la table @z avec les valeurs réelles classées.

Existe-t-il une meilleure façon de procéder ?

Answer 1

Je ne vais pas vous donner une solution complète, mais si j'avais besoin de données fractionnées comme celles-ci, j'essaierais d'utiliser sqlxml (vous devez l'essayer sur un grand nombre de lignes pour vérifier si les performances sont correctes pour vous) :

declare @x table (prm int,iin varchar(20))

insert into @x values(1, 'A/B/C')
insert into @x values(3, 'D')
insert into @x values(2, 'R/G')

select
    x.prm, x.iin, T.C.value('.', 'nvarchar(max)') as oout
from @x as x
    outer apply (
        select cast('<d>' + replace(x.iin, '/', '</d><d>') + '</d>' as xml) as Data
    ) as D
    outer apply D.Data.nodes('d') as T(C)

voir sql fiddle demo pour l'essayer.