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;

Cristian Magrini · Accepted Answer · 2016-03-19 11:22:20Z

2

Try with this

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;

edited Mar 19, 2016 at 11:22

answered Mar 19, 2016 at 11:16

Cristian Magrini

364 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

lonelydev101 Over a year ago

Nope, I ran on errors: on line 10 and line 1 - Incorrect syntax near '2016-03-03'. Also: Incorrect syntax near 'x'.

Cristian Magrini Over a year ago

But with edited post you want to obtain different data from first post.If you use variables you have to use exec sp_executesql because is dynamic query.

lonelydev101 Over a year ago

Great work! It works! Just a question: If I want to group by CreatedBy (employee) as variable and to check is it null or not null (I am planning to create stored procedure for this query), I would just add groupBy in @query?

lonelydev101 Over a year ago

Just to be clear, I want to implement additional variable, which I will fill from mvc5 program, like a filter which employee has entered on which days EventTypeID. Thanks again. You helped me a lot!

Cristian Magrini Over a year ago

Yes you add GROUP BY Statement at the end of @query. but you must remove data columns from select because there aren't include in aggregation functions. Ok but if you use that variable to filter data i think you have to introduce WHERE statement in this block ( 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 statement ) x

|

Collectives™ on Stack Overflow

T-SQL Dynamic columns Dates

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related