时间卡列的 SQL 日期(动态)

Question

Name   Date                           Duration
------ ------------------------------ ---------------------------------------
Leiuck 02/23/2014                     12.50
Annver 02/24/2014                     8.00
Leiuck 02/24/2014                     9.00
Mauler 02/24/2014                     8.00
Shaman 02/24/2014                     4.00
Annver 02/25/2014                     8.00
Leiuck 02/25/2014                     8.50
Mauler 02/25/2014                     8.00
Shaman 02/25/2014                     6.00
Annver 02/26/2014                     8.00
Leiuck 02/26/2014                     8.50
Shaman 02/26/2014                     7.00
Annver 02/27/2014                     8.00
Shaman 02/27/2014                     6.00

我需要日期作为列，持续时间作为结果。

select
    Name = left( per.Name, 3) + right( per.Name, 3)
,   Date = convert( varchar, t.EntryDate, 101)
,   Duration = sum(t.Duration)
from tentry t (nolock)
left join tpers per (nolock) on t.PersonID = per.PersonID
where
    t.EntryDate >= dateadd(wk, datediff(wk, 0, getdate()) -0, -1)
and t.EntryDate <= dateadd(wk, datediff(wk, 0, getdate()) -0,  5)
and per.Division = 1
and per.Act = 1
group by
    per.Name
,   t.EntryDate

请帮忙。

更多信息...

我需要输出看起来像这样。非常感谢你试图帮助我。

Name    2/23/2014   2/24/2014   2/25/2014   2/26/2014   2/27/2014
Annver              8           8           8           8
Leiuck  12.5        9           8.5         8.5         
Mauler              8           8                       
Shaman              4           6           7           6

Answer 1

DECLARE @Cols NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

SELECT @Cols = STUFF((SELECT DISTINCT ', ' + QUOTENAME([Date]) 
                      FROM TableName 
                      FOR XML PATH(''),TYPE).
                      value('.','NVARCHAR(MAX)'), 1, 2,'')
FROM TableName t


SET @Sql = N'SELECT *
             FROM TableName t
             PIVOT (SUM(Duration)
                    FOR [Date]
                    IN ('+ @Cols +')
                    )p'

EXECUTE sp_executesql @Sql

结果集

╔════════╦════════════╦════════════╦════════════╦════════════╦════════════╗
║  Name  ║ 2014-02-23 ║ 2014-02-24 ║ 2014-02-25 ║ 2014-02-26 ║ 2014-02-27 ║
╠════════╬════════════╬════════════╬════════════╬════════════╬════════════╣
║ Annver ║ NULL       ║ 8.00       ║ 8.00       ║ 8.00       ║ 8.00       ║
║ Leiuck ║ 12.50      ║ 9.00       ║ 8.50       ║ 8.50       ║ NULL       ║
║ Mauler ║ NULL       ║ 8.00       ║ 8.00       ║ NULL       ║ NULL       ║
║ Shaman ║ NULL       ║ 4.00       ║ 6.00       ║ 7.00       ║ 6.00       ║
╚════════╩════════════╩════════════╩════════════╩════════════╩════════════╝

WORKING SQL FIDDLE