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 @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]; */