sql - T-SQL 动态列日期

Question

我有一张像这张照片上的 table 。

我想成为:

EventTypeID|CreatedBy |2016-03-01 |2016-03-02  |2016-03-03|...
6          |    2     |     1     |      2     |      0   |... 
9          |    4     |     0     |      1     |      3   |...
...

我试过这个 T-SQL:

DECLARE 
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @startdate datetime,
    @enddate datetime,
    @paramdef nvarchar(max)

SET @startdate = '2013-02-01'
SET @enddate = '2013-05-10';
SET @paramdef = '@startdate datetime, @enddate datetime';

SELECT 
    @cols = STUFF((SELECT ',' + QUOTENAME(convert(varchar(10), [Date], 120)) 
                   FROM AdeccoView
                   --where datein > @startdate
                   --    and datein <= @enddate
                   GROUP BY [Date]
                   ORDER BY [Date]
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT EventTypeID, '+ @cols + '
        from 
        (
            select EventTypeID, 
                [Date] = convert(varchar(10), [Date], 120), 
                dttime = cast(cast(timeout  as varchar(5)),
                row_number() over(partition by ViewID, [Date] order by  [Date]) seq
            from AdeccoViews
            --where datein > @startdate
               -- and datein <= @enddate
        ) x
        pivot 
        (
            max(dttime)
            for [Date] in ('+@cols+')
        ) p '

exec sp_executesql @query, @paramdef, @startdate = @startdate, @enddate =      @enddate;

编辑:

这是我编辑过的版本..它看起来不错，但我正在努力处理局部变量@v_columns - "+@v_Columns+"附近的语法不正确。应为“.”、ID 或 QUOTED_ID。

DECLARE 
@v_Columns VARCHAR(MAX),
@v_StartDate DATETIME = '2011-11-01',
@v_EndDate DATETIME = '2011-11-05',
@v_Query VARCHAR(MAX)

--pivot and delimit values

SELECT @v_Columns = COALESCE(@v_Columns,'[') + convert(varchar, [Date], 111) + '],[' 
FROM 
(SELECT DISTINCT [Date] FROM AdeccoView) th
WHERE
th.[Date] BETWEEN @v_StartDate AND @v_EndDate

--delete last two chars of string (the ending ',[')

SET @v_Columns = SUBSTRING(@v_Columns, 1, LEN(@v_Columns)-2)



SELECT Name,[Event]
FROM 
(SELECT ViewID, emp.EmployeeD, c.EventTypeID, c.CreatedBy, emp.Name,c.[Date],     [Event]
 FROM
  AdeccoView c
  left join EventType r
  on c.EventTypeID = r.EventTypeID
  left join Employee emp on c.CreatedBy = emp.EmployeeD) p
PIVOT
(COUNT (EventTypeID) FOR [Date] IN ( '+ @v_Columns +' )) AS pvt
ORDER BY pvt.ViewID;

编辑 TOTAL 列:

我正在尝试设置总计列，但数据不佳。我在分区上使用了 COUNT(*):

DECLARE 
@cols AS NVARCHAR(MAX),
@selcols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX),
@startdate datetime,
@enddate datetime,
@paramdef nvarchar(max)

SET @startdate = '2013-02-01'
SET @enddate = '2013-05-10';
SET @paramdef = '@startdate datetime, @enddate datetime';


SELECT 
@selcols = STUFF((SELECT ','+'ISNULL(' + QUOTENAME(convert(varchar(10),  [Date], 120)) + ', 0) AS ' +  QUOTENAME(convert(varchar(10), [Date], 120)) 
               FROM AdeccoView
               --where datein > @startdate
               --    and datein <= @enddate
               GROUP BY [Date]
               ORDER BY [Date]
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')


SELECT 
@cols = STUFF((SELECT ',' + QUOTENAME(convert(varchar(10), [Date], 120)) 
               FROM AdeccoView
               --where datein > @startdate
               --    and datein <= @enddate
               GROUP BY [Date]
               ORDER BY [Date]
               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT CreatedBy,Name, Surname,EventTypeID,Event, '+ @selcols + ',Total
    from 
    (
        select av.EventTypeID,av.CreatedBy,emp.Name,emp.Surname,Event,
            convert(varchar(10), [Date], 120) [Date],
            row_number() over(PARTITION BY [date],av.EventTypeID,av.CreatedBy   order BY av.EventTypeID,av.CreatedBy ) m,
            Count(*) over(partition by av.EventTypeID) Total
        from AdeccoView av
        left join EventType et on et.EventTypeID = av.EventTypeID
        left join Employee emp on av.CreatedBy = emp.EmployeeD

    ) x
    pivot 
    (
        max(m)
        for [Date] in ('+@cols+')
    ) p '

exec sp_executesql @query, @paramdef, @startdate = @startdate, @enddate =@enddate;

Answer 1

试试这个

DECLARE 
    @cols AS NVARCHAR(MAX),
    @selcols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @startdate datetime,
    @enddate datetime,
    @paramdef nvarchar(max)

SET @startdate = '2013-02-01'
SET @enddate = '2013-05-10';
SET @paramdef = '@startdate datetime, @enddate datetime';


SELECT 
    @selcols = STUFF((SELECT ','+'ISNULL(' + QUOTENAME(convert(varchar(10), [Date], 120)) + ', 0) AS ' +  QUOTENAME(convert(varchar(10), [Date], 120)) 
                   FROM AdeccoView
                   --where datein > @startdate
                   --    and datein <= @enddate
                   GROUP BY [Date]
                   ORDER BY [Date]
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')


SELECT 
    @cols = STUFF((SELECT ',' + QUOTENAME(convert(varchar(10), [Date], 120)) 
                   FROM AdeccoView
                   --where datein > @startdate
                   --    and datein <= @enddate
                   GROUP BY [Date]
                   ORDER BY [Date]
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT EventTypeID, '+ @selcols + '
        from 
        (
            select EventTypeID,CreatedBy,
                convert(varchar(10), [Date], 120) [Date],
                row_number() over(PARTITION BY [date],EventTypeID,CreatedBy order BY EventTypeID,CreatedBy ) m
            from AdeccoView
            --where datein > @startdate
               -- and datein <= @enddate
        ) x
        pivot 
        (
            max(m)
            for [Date] in ('+@cols+')
        ) p '

exec sp_executesql @query, @paramdef, @startdate = @startdate, @enddate =@enddate;