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
|
USE [GMAO]
GO
/****** Object: StoredProcedure [dbo].[Eta_Amdec] Script Date: 10/13/2014 22:30:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @cols1 AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' +
QUOTENAME(Type_Intervention )
FROM View_Analyse_taux_20
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
select @cols1 = STUFF((SELECT distinct ',' +
QUOTENAME(Type_Intervention )+'NVARCHAR(max)'
FROM View_Analyse_taux_20
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, ' ');
DECLARE @SQLTable NVARCHAR(Max) = 'CREATE TABLE [Pivot]( Date datetime ,' +
@cols1+ ' )';
IF OBJECT_ID('Pivot') IS NOT NULL
DROP TABLE [Pivot];
execute(@SQLTable);
SELECT @query = 'INSERT INTO [GMAO].[dbo].[Pivot]( Date ,' +
@cols+ ' ) values (
SELECT * FROM dbo. View_Analyse_taux_20
pivot
( Max(NB_Intervention) FOR Type_Intervention IN(' + @cols + ')) AS p'
print @query;
execute(@query);
go |
Partager