0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @Columns as VARCHAR(MAX)
DECLARE @ColumnsSum as VARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME([tagname]),
       @ColumnsSum = COALESCE(@ColumnsSum + ', ','') + 'sum(' +  QUOTENAME([tagname]) + ') OVER (ORDER BY creationdate) as ' +tagname 
FROM
    (select top 5 t.tagname
     from posts p
     inner join posttags pt on pt.postid=p.id
     inner join tags t on t.id=pt.tagid
     where YEAR(p.creationdate) = YEAR(GETDATE()) and MONTH(p.creationdate) = MONTH(GETDATE())
     group by t.tagname
     order by count(*) desc
    ) AS B

DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 'select creationdate,
   ' + @ColumnsSum + '
from (

  select creationdate, ' + @Columns + '
  from 
  (
    select cast(p.creationdate as date) creationdate, t.tagname
    from posts p
    inner join posttags pt on pt.postid=p.id
    inner join tags t on t.id=pt.tagid
    where YEAR(p.creationdate) = YEAR(GETDATE()) and  MONTH(p.creationdate) = MONTH(GETDATE())
    ) src
  PIVOT 
  ( 
     count(tagname) -- what we aggregate on 
    FOR tagname  in (' + @Columns + ')
  ) pvt

) a
order by creationdate;
'


EXEC(@SQL);

fork query

edited dec 7

created dec 7 jon.doe37663