Преобразовывать результаты с помощью pivot в sqlserver - запрашивая последние 30 дней работы пользователя в MS Project 2012

Question

Я пытаюсь создать запрос, который покажет месяц работы пользователя в MS Project 2012.

У меня есть этот запрос:

выберите номер, данные, работу с (

select 
r.ResourceName Nome,
convert(varchar(10), ad.TimeByDay, 103) data,
sum(ad.AssignmentActualWork) Work

FROM
dbo.MSP_EpmAssignmentByDay ad,
dbo.msp_epmassignment a,
dbo.msp_epmresource r,
dbo.msp_epmtask t

WHERE 
a.TaskUID = t.taskuid
AND a.assignmentuid = ad.assignmentuid
AND a.ResourceOwnerUID = r.ResourceUID
AND ad.timebyday >= convert(varchar(10), '01/05/14', 103)
AND ad.timebyday <= convert(varchar(10), '31/05/14', 103)

group by r.ResourceName,ad.TimeByDay 
) t

This query print this result:
Name        Date        Sum(work)
Frederico   01/05/2014  0.000000
Frederico   02/05/2014  8.000000
Frederico   ...         0.000000
Frederico   31/05/2014  8.000000
Other user  01/05/2014  3.000000

Мне нужен следующий результат:

Name        |   01/05/14    |   02/05/14    |   ... |   31/05/14    |
Frederico        0.0            8.0             0.0     0.0
Other user       3.0            0.0             0.0     0.0

Спасибо за все!

Answer 1

Uhuuu! Я понял!

Это оно:

    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

set @cols = STUFF((SELECT ',' + QUOTENAME(convert(varchar(10), ad.TimeByDay, 103)) 
                    from dbo.MSP_EpmAssignmentByDay ad
                    WHERE 
                    ad.timebyday >= convert(varchar(10), '01/05/14', 103)
                    AND ad.timebyday <= convert(varchar(10), '31/05/14', 103)
                    group by ad.TimeByDay
                    order by ad.TimeByDay asc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT Nome, ' + @cols + ' 
            FROM
            (
            SELECT 
            r.ResourceName Nome,
convert(varchar(10), ad.TimeByDay, 103) data,
sum(ad.AssignmentActualWork) Work 
FROM
dbo.MSP_EpmAssignmentByDay ad,
dbo.msp_epmassignment a,
dbo.msp_epmresource r,
dbo.msp_epmtask t

WHERE 
1=1
AND a.TaskUID = t.taskuid
AND a.assignmentuid = ad.assignmentuid
AND a.ResourceOwnerUID = r.ResourceUID
AND ad.timebyday >= convert(varchar(10), ''01/05/14'', 103)
AND ad.timebyday <= convert(varchar(10), ''31/05/14'', 103)


group by r.ResourceName,ad.TimeByDay

) t
        PIVOT
        (   SUM([work])
            FOR data IN ('+  @cols +')
        ) pvt;

' 



execute(@query)