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
|
-- Création de la liste des Jalons pour la table PivJL
;WITH
CTE_LISTE_COLONNES AS
(
SELECT @numPremiereColonne Indice
UNION ALL
SELECT Indice + 1
FROM CTE_LISTE_COLONNES
WHERE Indice < @numDerniereColonne
)
SELECT @ListeJL = ISNULL(@ListeJL, '') + (select JL from #tmp_JL_SGA where id = Indice) + ', '
FROM CTE_LISTE_COLONNES
-- Suppression de la dernière virgule de la chaine
SELECT @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1)
SELECT @ListeJL = SUBSTRING(@ListeJL, 1, LEN(@ListeJL) - 1)
-- Ajout de l'instruction CREATE TABLE
SET @SQL = 'CREATE TABLE ' + @nomTable + '(DATE datetime, MATR char(4), [OF] char(9),I char(1), ' + @SQL + ')'
EXEC (@SQL)
-------------
-- Pivot Jalon : Insertion des valeurs
declare @chaine varchar(max)
set @chaine = '
Insert into dbo.PivJL '
+ ' select convert(varchar,Datreal,103) as date, matr, [OF], I, ' + @listeJL
+ ' from '
+ ' (select Datreal, matr, [OF],I,JL,MR_Q from dw_analyse_rebut ) d ' -- where datreal between '''+@datedeb+''' and '''+@datefin+''' and micro IN (SELECT ITEM FROM [DBO].[FCTSPLITTOCHAR]('''+@Micro+''', '',''))) d '
+ 'pivot '
+ '( '
+ 'sum(MR_Q) '
+ 'For JL in '
+ ' ('+@listeJL+') '
+ ') AS pvt '
+ 'where [OF] = ''50942074'' '
--print @chaine
exec (@chaine)
--select * from dbo.PivJL |
Partager