Дядька, можИт если наткнёшься на оракловый солюшн типа такого что снизу - буду тебе должОн пиво.
ALTER procedure [Tasks].[CountTaskByTaskName]
(
@m char(2)
,@y char(4)
)
as
goto Error
Main:
DECLARE @TaskColumns NVARCHAR(4000)
DECLARE @TaskWhere NVARCHAR(2000)
set @TaskColumns = ''
SELECT @TaskColumns = @TaskColumns + '[' + [a].[Column] + '] AS '+''''+[a].[Column] +''''+','
FROM
(
SELECT DISTINCT NameOfTask as [Column]
FROM [DW].[Tasks].[Task] t,
Tasks.DimTaskActivityType a
where t.[TaskActivityTypeFK]=a.ID
) as a
SET @TaskWhere = ''
SELECT @TaskWhere = @TaskWhere + '[' + [b].[Column] + '],'
FROM
(
SELECT DISTINCT NameOfTask as [Column]
FROM [DW].[Tasks].[Task] t,
Tasks.DimTaskActivityType a
where t.[TaskActivityTypeFK]=a.ID
) as b
SET @TaskColumns = Left(@TaskColumns, Len(@TaskColumns) - 1)
SET @TaskWhere = Left(@TaskWhere, Len(@TaskWhere) -1)
--select @TaskColumns
declare @q varchar(4000), @q1 varchar(4000), @q2 varchar(100), @q3 varchar(100)
set @q =
N'SELECT data, '
+ @TaskColumns +
' FROM
(
SELECT convert(char(8), PDateOfCreation, 112) as data, t.ID, NameOfTask
FROM [DW].[Tasks].[Task] t,
Tasks.DimTaskActivityType a
where t.[TaskActivityTypeFK]=a.ID
and month(PDateOfCreation)=
'
+@m+
'
and year(PDateOfCreation)=
'
+@y+
'
) AS p
[b]PIVOT[/b] ( count (ID ) FOR NameOfTask IN ( '
+ @TaskWhere +
')) AS pt order by data'
exec ( @q )
Error:
if (@m is null or @y is null)
begin
set @m=cast(month(dateadd(m, -1, getdate())) as char(2))
set @y=cast(year(dateadd(m, -1, getdate())) as char(4))
goto Main
end