4

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

select dbid, name
into #dbs
from master..sysdatabases
where name not in ('Data.StackExchange', 'master', 'model', 'msdb', 'tempdb')
  and right(name, 5) <> '.Meta'
insert into #dbs VALUES(38, 'StackExchange.Meta')

create table #topusers (
  accountid int,
  userid int,
  rep int,
  site varchar(40)
)

declare @id int, @db varchar(40), @sql varchar(max)

while (select count(*) from #dbs) > 0
begin
  select top 1 @id = dbid, @db = name from #dbs
  set @sql = '
    insert into #topusers
    select AccountId, Id, Reputation, ''' + @db + '''
    from [' + @db + ']..[Users]
    where Reputation > 5000'
  exec (@sql)
  delete from #dbs where dbid = @id
end

--select account from #topusers
--group by accountid

SELECT 
  accountid as [Account Id],
  count(accountid) as [Site count],
  STUFF((
    SELECT ', ' + site + ':' + CAST(rep AS VARCHAR(MAX)) 
    FROM #topusers 
    WHERE (accountid = u.accountid) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS [Site:rep]
FROM #topusers u
GROUP BY accountid
ORDER BY count(accountid) desc

view current version

edited aug 22 14

created aug 22 14 TheBanana