pivoted_content_types_compiled.sql

with
    source as (

    select distinct 
        oct.org_id as org_id, 
        ct.content_type_name as content_type_name
    from fxdmz.dev_brian_orgs.org_content_types oct
    join fxdmz.dev_brian_base.content_types ct
    on oct.content_type_id = ct.content_type_id

    ),

    final as (

        select
            org_id,
                count(case when content_type_name = 'Sports Broadcast' then 1 end) as sports_broadcast, 
                count(case when content_type_name = 'Unscripted Content' then 1 end) as unscripted_content, 
                count(case when content_type_name = 'Documentary' then 1 end) as documentary, 
                count(case when content_type_name = 'PC' then 1 end) as pc, 
                count(case when content_type_name = 'Game Shows' then 1 end) as game_shows, 
                count(case when content_type_name = 'Visualization' then 1 end) as visualization, 
                count(case when content_type_name = 'Console' then 1 end) as console, 
                count(case when content_type_name = 'Reality Content' then 1 end) as reality_content, 
                count(case when content_type_name = 'DesignViz' then 1 end) as designviz, 
                count(case when content_type_name = 'News Broadcast' then 1 end) as news_broadcast, 
                count(case when content_type_name = 'Location-Based Content' then 1 end) as location_based_content, 
                count(case when content_type_name = 'Feature' then 1 end) as feature, 
                count(case when content_type_name = 'Text-Based Content' then 1 end) as text_based_content, 
                count(case when content_type_name = 'Trailers' then 1 end) as trailers, 
                count(case when content_type_name = 'Mobile' then 1 end) as mobile, 
                count(case when content_type_name = 'Advertising' then 1 end) as advertising, 
                count(case when content_type_name = 'Traditional Content' then 1 end) as traditional_content, 
                count(case when content_type_name = 'Video Game Content' then 1 end) as video_game_content, 
                count(case when content_type_name = 'Web' then 1 end) as web, 
                count(case when content_type_name = 'Books' then 1 end) as books, 
                count(case when content_type_name = 'Scripted Content' then 1 end) as scripted_content, 
                count(case when content_type_name = 'Mixed-Media Performance' then 1 end) as mixed_media_performance, 
                count(case when content_type_name = 'Cloud' then 1 end) as cloud, 
                count(case when content_type_name = 'Explainer' then 1 end) as explainer, 
                count(case when content_type_name = 'Performance-Based Content' then 1 end) as performance_based_content, 
                count(case when content_type_name = 'Music Videos' then 1 end) as music_videos, 
                count(case when content_type_name = 'Design' then 1 end) as design, 
                count(case when content_type_name = 'Mixed-Media Exhibit' then 1 end) as mixed_media_exhibit, 
                count(case when content_type_name = 'Cinematics' then 1 end) as cinematics, 
                count(case when content_type_name = 'Short Form' then 1 end) as short_form, 
                count(case when content_type_name = 'Mixed-Media Installation' then 1 end) as mixed_media_installation, 
                count(case when content_type_name = 'Audio-Based Content' then 1 end) as audio_based_content, 
                count(case when content_type_name = 'Episodic' then 1 end) as episodic, 
                count(case when content_type_name = 'Social Content' then 1 end) as social_content, 
                count(case when content_type_name = 'Corporate' then 1 end) as corporate, 
                count(case when content_type_name = 'Promotional Content' then 1 end) as promotional_content, 
                count(case when content_type_name = 'Talk Shows' then 1 end) as talk_shows, 
                count(case when content_type_name = 'Mixed-Media Attraction' then 1 end) as mixed_media_attraction

        from source
        group by org_id

    )

select *
from final