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
|
USE [POWER_SBO]
DECLARE @PrevDate Datetime
DECLARE @IndexPériode Int
DECLARE @IndexBoucle Int
DECLARE @Ci Datetime
DECLARE @Cf Datetime
--DECLARE @LOOPOUT TABLE
--(
-- PrevMonth VARCHAR(25), NBJ INT -- La variable table ne passe pas à dans l'exec() => Utilisation d'une vrai table
--)
SET @PrevDate = CONVERT (DATETIME, '01/01/2017', 103)
SET @IndexPériode = 17
SET @Ci = CONVERT (DATETIME, '15/03/2016', 103)
SET @Cf = CONVERT (DATETIME, '14/03/2017', 103)
SET @IndexBoucle = 0
DELETE FROM dbo.LOOPOUT
WHILE @IndexBoucle <= @IndexPériode
BEGIN
INSERT INTO LOOPOUT SELECT * FROM POWER_SBO.dbo.Rep_n(@PrevDate,@IndexBoucle,@Ci,@Cf)
SET @IndexBoucle = @IndexBoucle + 1
END
--SELECT * FROM LOOPOUT
DECLARE col_cursor CURSOR FOR SELECT PrevMonth FROM LOOPOUT
DECLARE @col nvarchar(25), @col_concat nvarchar(1000), @pivot_in nvarchar(1000), @sql nvarchar(1000)
SELECT @col_concat = '', @sql = ''
OPEN col_cursor
FETCH col_cursor INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @col_concat = @col_concat + '['+ @col +'], '
FETCH col_cursor INTO @col
END
CLOSE col_cursor
DEALLOCATE col_cursor
SELECT @pivot_in = LEFT(@col_concat, LEN(@col_concat)-1)
SELECT @col_concat = '''Valeur''' + ' AS [Mois], ' + @col_concat
SELECT @col_concat = LEFT(@col_concat, LEN(@col_concat)-1)
--SELECT @col_concat -- Visu de contrôle
--SELECT @pivot_in -- Visu de contrôle
SELECT @sql = 'SELECT ' + @col_concat +
'FROM LOOPOUT
PIVOT (AVG(NBJ)
FOR PrevMonth IN (' + @pivot_in + ')) AS Row_As_Line'
exec(@sql) |
Partager