2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in conceptual questions about life and challenges in a world where "cognitive" functions can be mimicked in purely digital environment

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

declare @pivotCols2 nvarchar(max);
select @pivotCols2 = (select string_agg(quotename([Name]), ',')
  from (select [Name]
          from #ModeratorElectionBadges
         group by [Name]
         order by [Name] offset 0 rows) as otherwise_sql_complains);

select @pivotCols1;
select @pivotCols2;

/*
exec('
select [UserId], [BadgeName], [HasBadge] from (
select [UserId], [BadgeName], [HasBadge] 
  from #UserModeratorElectionBadges
-- order by [BadgeName]
) as src
pivot (
  max([BadgeName])
  for
  [UserId] in ( ' + @pivotCols + ' )
) 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