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
|
WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num,
CAST('<a>' + REPLACE(case when left(v5_field,1) = '' then '-1-1' + v5_field else v5_field end, ' ', '</a><a>') + '</a>' AS XML) AS col_xml
,v5_stock, v5_validate,v5_field
FROM v5
),
CTE2
AS
(
SELECT
num,
ROW_NUMBER() OVER (PARTITION BY num ORDER BY (SELECT 0)) AS num2,
RES.value('(./text())[1]', 'VARCHAR(50)') as valeur
,v5_stock, v5_validate,v5_field
FROM CTE
CROSS APPLY col_xml.nodes('//a') AS T(RES)
WHERE RES.value('(./text())[1]', 'VARCHAR(50)') IS NOT NULL
--;
)
SELECT t.num
, t2.valeur as col1
, t3.valeur as col2
, t4.valeur as col3
, t5.valeur as col5
, t.v5_stock, t.v5_validate, t.v5_field
FROM CTE2 AS T
inner join CTE2 as T2 on T2.num = T.num and t2.num2 = 1
inner join CTE2 as T3 on T3.num = T.num and t3.num2 = 2
inner join CTE2 as T4 on T4.num = T.num and t4.num2 = 3
inner join CTE2 as T5 on T5.num = T.num and t5.num2 = 4
GROUP BY T.num, t2.valeur,t3.valeur,t4.valeur,t5.valeur,t.v5_stock, t.v5_validate, t.v5_field; |
Partager