Discussion :

[droog]

Bonjour à tous,

Je coince depuis quelques jours sur un problème de requête qui me permettrait de faire un pivot sur des données texte.
Le pivot permet de transposer des données de lignes en colonnes, et d'effectuer un calcul sur un ou plusieurs champs en fonction des données passées en colonnes.
Dans mon cas je ne veux pas effectuer de calcul, mais seulement transposer mes données de cette façon :



Les données à gauche sont obtenues avec STRING_SPLIT à partir de chaines de caractères correpondant à des valeurs séparées par un ';' et contenues dans un champ unique d'une table.
J'obtiens ce jeu de données avec cette requête :

Code :

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

1
2
3
4
5
SELECT
    	value, 
    	'Field'+CONVERT(varchar,ROW_NUMBER()  OVER (PARTITION BY data ORDER BY CHARINDEX( value+';', data +';')) ) field
    FROM mytable t
    	CROSS APPLY STRING_SPLIT(t.data, ';') AS s

L'étape suivante serait donc de transposer ces informations de façon à obtenir ce que vous voyez à droite dans l'image.
J'ai commencé à regarder du côté de PIVOT, mais cela ne me permet pas d'obtenir toutes mes données.
Pour l'instant j'utilise cette requête pour traiter un enregistrement à la fois, et cela fonctionne bien sûr :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 SELECT * FROM( 
    SELECT
    	value, 
    	'Field'+CONVERT(varchar,ROW_NUMBER()  OVER (PARTITION BY data ORDER BY CHARINDEX( value+';', data +';')) ) field
    FROM (select @mychaine data) t
    	CROSS APPLY STRING_SPLIT(t.data, ';') AS s
    ) q
    PIVOT
      (
      MAX(q.value)
      FOR field IN ([field1], [field2], [field3], [field4], [field5])
      ) AS fields

J'aimerai le faire sur l'ensemble des enregistrements si c'est possible.
Si vous avez des idées je suis preneur !
Merci d'avance pour votre aide

droog

[JeitEmgie]

Pas le temps de remonter une VM SQL Server alors voici une solution ORACLE que vous devez pouvoir transposer facilement :

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
create table t_data
(
    nsq number(19,0) primary key,
    txt varchar2(4000 char)
);
/
 
 
insert all 
    into t_data (nsq, txt) values(1 ,'t1;t2;t3;t4;t5')
    into t_data (nsq, txt) values(2, 't6;t7;t8;t9;t10')
    into t_data (nsq, txt) values(3, 't11;t12;t13;t14;t15')
    into t_data (nsq, txt) values(4, 't16;t17;t18;t19;t20')
select 1 from dual ;
commit ;
/
 
 
create or replace type result_obj as object (
    nsq number(19,0),
    idx number(10,0),
    colname varchar2(32 char),
    val varchar2(256 char)
);
/
 
 
create type result_obj_t as table of result_obj ;
/
 
 
create or replace  function prepare_result_4pivot 
return result_obj_t 
pipelined as
    cnt number(10,0) := 1 ;
begin 
    for rec in (
        select * from t_data
    )
    loop 
        cnt := 1 ;
        for subrec in (
            select subs from json_table(
                    replace( json_array(
                        replace(rec.txt,';' , ',')
                    ), ',', '","')
            ,
              '$[*]' columns (
                subs varchar2(4000) path '$'
              )
            )   
        )
        loop
            pipe row( result_obj( rec.nsq, cnt, 'Field' || cnt, subrec.subs ) ) ; 
            cnt := cnt + 1 ;
        end loop ;
    end loop ;
end prepare_result_4pivot ;
/
 
 
select * from (select nsq, colname, val from table(prepare_result_4pivot) order by nsq, idx )
pivot (
    max(val) for colname in ('Field1','Field2','Field3','Field4','Field5') 
)
;

Code :

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

1
2
3
4
5
 
1    t1    t2    t3    t4    t5
2    t6    t7    t8    t9    t10
4    t16    t17    t18    t19    t20
3    t11    t12    t13    t14    t15

(NB
il existe aussi une solution plus simple mais à mon avis plus difficile à transposer en SQL Server :

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
 
with data as (
    select 1 as nsq, 't1;t2;t3;t4;t5' as txt from dual
    union all
    select 2 as nsq, 't6;t7;t8;t9;t10' as txt from dual
    union all
    select 3 as nsq, 't11;t12;t13;t14;t15' as txt from dual
    union all
    select 4 as nsq, 't16;t17;t18;t19;t20' as txt from dual
),
expanded as (
    select d.nsq, regexp_substr( d.txt, '[^;]+', 1, details.column_value) as val,
        'Field' || row_number() over(partition by nsq order by rownum) as c,
        row_number() over(partition by nsq order by rownum) as idx
    from data d,
        table(
            cast(multiset(
                select level from dual connect by  level <= length (regexp_replace(d.txt, '[^;]+'))  + 1
            ) as sys.OdciVarchar2List)
        ) details
)
select * from (
    select  nsq, val, c from expanded order by nsq, idx
)
pivot (
    max(val) for c in ('Field1','Field2','Field3','Field4','Field5') 
)
order by nsq
;

)

[droog]

Bonjour JeitEmgie, et merci beaucoup d'avoir pris le temps de répondre à mon problème.
Je prend le temps de regarder ça cette semaine pour tester ce que je peux faire en TransactSQL en m'inspirant de ta solution PLSQL.
Merci et à plus tard

droog

[aieeeuuuuu]

bonjour,

il suffit d'appliquer le PIVOT au résultat de la fonction table STRING_PLIT.

Mais ne pouvez-vous pas plus simplement réécrire cette fonction pour qu'elle renvoie directement les données sous la forme que vous voulez ?

Un petit jeu de données serait le bienvenu pour nous aider à y voir plus clair et mieux vous aider

[JeitEmgie]

En fait la deuxième solution est plus simple à transformer car la difficulté de remplacer le "connect by" est en fait triviale car votre nombre de colonnes "Field" est fixe : 5,
donc

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
 
with data as (
    select 1 as nsq, 't1;t2;t3;t4;t5' as txt from dual
    union all
 
    select 4 as nsq, 't16;t17;t18;t19;t20' as txt from dual
    union all
    select 2 as nsq, 't6;t7;t8;t9;t10' as txt from dual
    union all
    select 3 as nsq, 't11;t12;t13;t14;t15' as txt from dual
),
expanded as (
    select d.nsq, regexp_substr( d.txt, '[^;]+', 1, details.column_value) as val,
        'Field' || row_number() over(partition by nsq order by rownum) as c,
        row_number() over(partition by nsq order by rownum) as idx
    from data d,
        table(
            select apex_string.split('1:2:3:4:5',':') from dual
        ) details
)
select * from (
    select  nsq, val, c from expanded order by nsq, idx
)
pivot (
    max(val) for c in ('Field1','Field2','Field3','Field4','Field5') 
)
order by nsq
;

et apex_string.split deviendra "string_split" en SQL Server et la concaténation de string "||" deviendra "+" ...
reste regexp_substr à remplacer...


ceci devrait fonctionner :

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
 
with expanded as (
    select d.nsq, 
        (
         select value from (
             select row_number() over(order by (SELECT 1)) as rn, value from string_split(d.txt, ';')
         ) x where rn = details.value
        )  
        as val,
        'Field' + cast(row_number() over(partition by nsq order by (select 1)) as varchar) as c,
        row_number() over(partition by nsq order by (select 1)) as idx
    from data d,
        (
            select value from string_split('1:2:3:4:5',':') 
        ) details
)
select * from (
    select  nsq, val, c from expanded 
) q
pivot (
    max(val) for c in ([Field1],[Field2],[Field3],[Field4],[Field5]) 
) as Fields
;