0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for users of TeX, LaTeX, ConTeXt, and related typesetting systems

EXEC dbo.sede_ineachdb @SQLCommand = N'

SELECT Url,
       reputation,
       question_count,
       answer_count,
       [bronze badges] = COALESCE([1], 0),
       [silver badges] = COALESCE([2], 0),
       [gold badges]   = COALESCE([3], 0)
FROM 
(
  SELECT su.user_id, 
         su.reputation,
         su.question_count,
         su.answer_count,
         Url = CONCAT([$site_url$], ''/users/'', 
               su.user_id, ''/?tab=badges&sort=class|'', [$long_name$])
    FROM dbo.sede_users AS su
   WHERE su.account_id = ##AccountId:int##
     AND su.site_id = [$site_id$]
) AS src
OUTER APPLY
    (
      SELECT class, BadgeCount = COUNT(*)
        FROM dbo.Badges AS b
       WHERE b.UserId = src.user_id
       GROUP BY class
    ) AS b
 PIVOT
 (
   MAX(BadgeCount) 
   FOR [class] IN ([1],[2],[3])
 ) AS p;';

fork query

created apr 19 Aaron Bertrand