T-SQL Dynamic columns Dates

Question

I have a table like on this photo.

I want to be like:

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

I've tried this 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;

EDIT:

This is my editied version..it seems to be good, but I am struggling with local variable @v_columns - Incorrect syntax near "+@v_Columns+". Expecting '.', ID, or 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;

EDIT with TOTAL column:

I am trying to set up Total column, but data ain't good. I used COUNT(*) over partition:

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;

T-SQL Dynamic columns Dates

1 Answers