Non-series tags on Anime.SE

1

Please login or register to vote for this query.

(click on this box to dismiss)

https://anime.meta.stackexchange.com/q/1199

Computational Science

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results