https://anime.meta.stackexchange.com/q/1199
Q&A for scientists using computers to solve scientific problems
CREATE TABLE #NonSeriesTags ( TagType nvarchar(50) , TagName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS ) INSERT INTO #NonSeriesTags VALUES ('characters', 'domo-kun') , ('characters', 'hatsune-miku') , ('characters', 'os-tan') , ('characters', 'super-sonico') , ('companies', 'anime-planet') , ('companies', 'crunchyroll') , ('companies', 'funimation') , ('companies', 'gainax') , ('companies', 'key') , ('companies', 'kyoto-animation') , ('companies', 'madhouse') , ('companies', 'myanimelist') , ('companies', 'netflix') , ('companies', 'pixiv') , ('companies', 'sentai-filmworks') , ('companies', 'studio-ghibli') , ('companies', 'type-moon') , ('conventions', 'anime-expo') , ('conventions', 'comiket') , ('fandom', 'amv') , ('fandom', 'conventions') , ('fandom', 'cosplay') , ('fandom', 'doujinshi') , ('fandom', 'itasha') , ('fandom', 'merchandise') , ('general', 'crossovers') , ('general', 'culture') , ('general', 'japanese-language') , ('general', 'light-novel-site') , ('general', 'meme') , ('general', 'music') , ('general', 'panel-arrangement') , ('general', 'quotes') , ('general', 'resources') , ('general', 'sign-language') , ('general', 'streaming-sites') , ('general', 'symbolism') , ('general', 'terminology') , ('general', 'theme-song') , ('general', 'tourism') , ('general', 'tropes') , ('industries', 'anime-history') , ('industries', 'anime-industry') , ('industries', 'anime-production') , ('industries', 'censorship') , ('industries', 'copyright') , ('industries', 'events') , ('industries', 'game-history') , ('industries', 'law') , ('industries', 'licensing') , ('industries', 'light-novel-history') , ('industries', 'light-novel-production') , ('industries', 'live-action-production') , ('industries', 'localization') , ('industries', 'mangaka') , ('industries', 'manga-history') , ('industries', 'manga-production') , ('industries', 'promotional-videos') , ('industries', 'visual-novel-production') , ('industries', 'voice-acting') , ('other-reified', 'anime-no-chikara') , ('other-reified', 'vocaloid') , ('people', 'aimer') , ('people', 'barasui') , ('people', 'claris') , ('people', 'hayao-miyazaki') , ('people', 'hideaki-anno') , ('people', 'hiroyuki-sawano') , ('people', 'jun-maeda') , ('people', 'kajiura-yuki') , ('people', 'kazuki-nakashima') , ('people', 'kazuki-takahashi') , ('people', 'ken-akamatsu') , ('people', 'kouji-seo') , ('people', 'maaya-sakamoto') , ('people', 'makoto-shinkai') , ('people', 'mamiko-noto') , ('people', 'mamoru-hosoda') , ('people', 'mamoru-oshii') , ('people', 'masamune-shirow') , ('people', 'sachika-misawa') , ('people', 'shun-saeki') , ('publications', 'ribon') , ('publications', 'shonen-jump') , ('regions', 'australia') , ('regions', 'england') , ('regions', 'europe') , ('regions', 'germany') , ('regions', 'greece') , ('regions', 'india') , ('regions', 'japan') , ('regions', 'north-korea') , ('regions', 'singapore') , ('regions', 'spain') , ('regions', 'united-states') , ('special', 'identification-request') , ('special', 'retag') ; SELECT #NonSeriesTags.TagType , #NonSeriesTags.TagName , STRING_AGG(TagSynonyms.SourceTagName, ', ') WITHIN GROUP (ORDER BY TagSynonyms.SourceTagName) AS [TagSynonyms] , Tags.[Count] AS QuestionsTagged /*, 'site://tags/' + #NonSeriesTags.TagName + '/info|[' + #NonSeriesTags.TagName + ']' AS [TagInfoLink] , TagExcerptPosts.Body AS TagExcerpt*/ FROM #NonSeriesTags LEFT JOIN Tags ON #NonSeriesTags.TagName = Tags.TagName LEFT JOIN TagSynonyms ON Tags.TagName = TagSynonyms.TargetTagName LEFT JOIN Posts AS TagExcerptPosts ON TagExcerptPosts.PostTypeId = 4 AND Tags.ExcerptPostId = TagExcerptPosts.Id GROUP BY #NonSeriesTags.TagType , #NonSeriesTags.TagName , Tags.[Count] , TagExcerptPosts.Body ORDER BY #NonSeriesTags.TagType , #NonSeriesTags.TagName