Q&A about the site for users of TeX, LaTeX, ConTeXt, and related typesetting systems
SELECT account_id, site_id, user_id INTO #MyUsers FROM dbo.sede_users WHERE account_id = ##AccountId:int##; EXEC dbo.sede_ineachdb @SQLCommand = N' SELECT Url, [bronze badges] = COALESCE([1], 0), [silver badges] = COALESCE([2], 0), [gold badges] = COALESCE([3], 0) FROM ( SELECT u.Id, Url = CONCAT([$site_url$], ''/users/'', u.Id, ''/?tab=badges&sort=class|'', [$site_name$]) FROM dbo.Users AS u INNER JOIN #MyUsers AS mu ON mu.user_id = u.Id WHERE u.AccountId = mu.account_id AND mu.site_id = [$site_id$] ) AS src OUTER APPLY ( SELECT class, BadgeCount = COUNT(*) FROM dbo.Badges AS b WHERE b.UserId = src.Id GROUP BY class ) AS b PIVOT ( MAX(BadgeCount) FOR [class] IN ([1],[2],[3]) ) AS p;';