2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

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 bit) 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 @pivotCols nvarchar(max);
select @pivotCols = @pivotCols + quotename([Name]) + ','
  from #ModeratorElectionBadges;
select @pivotCols = trim(',' from @pivotCols);

select @pivotCols;

/*
select [UserId], [BadgeName], [HasBadge] from (
select [UserId], [BadgeName], [HasBadge] 
  from #UserModeratorElectionBadges
-- order by [BadgeName]
) as src
pivot (
  max(BadgeName)
  for
  BadgeName in ( select BadgeName from #ModeratorElectionBadges )
) as pvt
 order by UserId
;
*/

/*
select distinct
       usr.[Id]   as [UserId],
       bdg.[Name] as [BadgeName]
  into #UserModeratorElectionBadges
  from #UserIds usr
           left join
       [Badges] bdg on bdg.[UserId] = usr.[Id]
                   and bdg.[Name] in (select [Name] from #ModeratorElectionBadges)
;

select * from #UserModeratorElectionBadges;
*/

/*
select distinct
       ume.[UserId],
       bdg.[Name],
       iif(ume.[UserId] is null, 0, 1) as [HasBadge]
  from #ModeratorElectionBadges     meb
           join
       [Badges]                     bdg on bdg.[Name]      = meb.[Name]
           left join
       #UserModeratorElectionBadges ume on ume.[BadgeName] = meb.[Name]
 order by bdg.[Name];
*/

/*
select distinct
       ume.[UserId],
       bdg.[Name],
       iif(ume.[UserId] is null, 0, 1) as [HasBadge]
  from #ModeratorElectionBadges     meb
           join
       [Badges]                     bdg on bdg.[Id]      = meb.[Id]
           left join
       #UserModeratorElectionBadges ume on ume.[BadgeId] = meb.[Id]
 order by bdg.[Name];
*/

view current version

edited jul 16 20

created jul 16 20 Ian Kemp