0

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackexchange.com/questions/273645/trends-on-stack-overflow

Q&A about the site for people who build and repair bicycles, people who train cycling, or commute on bicycles

select *
into #selectedtags
from 
(
-- add unions for tags as much as you like
      select N'jms' as tagname
union select N'activemq'
union select N'ibm-mq'
union select N'spring-jms'
--union select N'rabbitmq'
union select N'azureservicebus'

) as selection


declare tag_cur cursor for 
        select tagname 
        from #selectedtags
        order by tagname
declare @curr_tag nvarchar(25)
declare @main_insert nvarchar(max)

set @main_insert = 'select dateadd(m,  -datediff(m, p.creationdate, getdate()),getdate())'

open tag_cur;
fetch next from tag_cur into @curr_tag
WHILE @@FETCH_STATUS = 0
BEGIN
-- use a different aggregate here if needed
set @main_insert = @main_insert + ', sum(case 
          when t.tagname ='''+ @curr_tag + ''' 
          then 1 
          else 0
          end) as ['+ @curr_tag+']';
fetch next from tag_cur into @curr_tag
END
close tag_cur;
set @main_insert = @main_insert + '
from posts p
inner join posttags pt 
        on pt.postid = p.id
inner join tags t 
        on t.id = pt.tagid
inner join #selectedtags st 
        on st.tagname = t.tagname collate database_default
and p.creationdate < eomonth(getdate(),-1) -- full month only
group by datediff(m, p.creationdate, getdate()) -- whole months
order by 1 desc'

print @main_insert
exec (@main_insert)

/*
-- this is what the query looks like that gets dynamically generated
select dateadd(m,  -datediff(m, p.creationdate, getdate()),getdate())
     , sum(case 
          when t.tagname ='python' 
          then 1 
          else 0
          end) as [Python]
     , sum(case 
          when t.tagname ='java' 
          then 1 
          else 0
          end) as [java]
     , sum(case 
          when t.tagname ='c#' 
          then 1 
          else 0
          end) as [c#]
from posts p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
inner join #selectedtags st on st.tagname = t.tagname collate database_default
and p.creationdate < eomonth(getdate(),-1)
group by datediff(m, p.creationdate, getdate())
order by 1 desc
*/

fork query

edited aug 16 19 anonymous

created feb 27 16 rene