Moderator election badges

1

Please login or register to vote for this query.

(click on this box to dismiss)

Interpersonal Skills

Q&A for people looking to improve their interpersonal communication skills

select distinct
       [Id]
  into #UserIds
  from [Users]
 where [Id] in ( ##UserIds## )
;

select distinct
       bdg.[Name]
  into #ModeratorElectionBadges
  from [Badges] bdg
 where bdg.[Name] in (
         'Civic Duty', 
         'Cleanup',
         'Constituent',
         'Convention',
         'Copy Editor',
         'Deputy',
         'Electorate',
         'Enthusiast',
         'Explainer',
         'Investor',
         'Marshal',
         'Organizer',
         'Quorum',
         'Refiner',
         'Reviewer',
         'Sportsmanship',
         'Steward', 
         'Strunk & White',
         'Tag Editor',
         'Yearling'
)
;

select usr.[Id]           as [UserId],
       meb.[Name]         as [BadgeName],
       cast(0 as tinyint) as [HasBadge]
  into #UserModeratorElectionBadges
  from #UserIds                 usr
           cross join
       #ModeratorElectionBadges meb
;

update ume
   set ume.[HasBadge] = 1
  from #UserModeratorElectionBadges ume
           join
       [Badges]                     bdg on bdg.[Name]   = ume.[BadgeName]
                                       and bdg.[UserId] = ume.[UserId]
;

declare @pivotColumnNames nvarchar(max) = '';
select @pivotColumnNames = @pivotColumnNames + quotename([Name]) + ','
  from #ModeratorElectionBadges
 order by [Name];
select @pivotColumnNames = trim(',' from @pivotColumnNames);

declare @pivotColumnNamesAsBits nvarchar(max) = '';
select @pivotColumnNamesAsBits = @pivotColumnNamesAsBits + 'cast(' + quotename([Name]) + ' as bit) as ' + quotename([Name]) + ','
  from #ModeratorElectionBadges
 order by [Name];
select @pivotColumnNamesAsBits = trim(',' from @pivotColumnNamesAsBits);

declare @dynamicPivotSql nvarchar(max) = '
select [UserId],
       ' + @pivotColumnNamesAsBits + '
  from (
select [UserId],
       [BadgeName],
       [HasBadge]
  from #UserModeratorElectionBadges
) as src
pivot (
  min([HasBadge])
  for [BadgeName] in ( ' + @pivotColumnNames + ' )
) as pvt
 order by [UserId]
;
';

/*
select @dynamicPivotSql
;
*/

exec sp_executesql @dynamicPivotSql
;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results