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