Untitled

-- jsamdm_dev.mdm_product_field_view source

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `mdm_product_field_view` AS
select
    `pf`.`RECORD_ID_2` AS `RECORD_ID_2`,
    max(if((`pf`.`FIELD_NAME` = 'COMARKETING_PARTNER'), `pf`.`FIELD_VALUE`, NULL)) AS `COMARKETING_PARTNER`,
    max(if((`pf`.`FIELD_NAME` = 'MARKETED_BY'), `pf`.`FIELD_VALUE`, NULL)) AS `MARKETED_BY`,
    max(if((`pf`.`FIELD_NAME` = 'JJ_OPERATING_COMPANY'), `pf`.`FIELD_VALUE`, NULL)) AS `JJ_OPERATING_COMPANY`,
    max(if((`pf`.`FIELD_NAME` = 'AESPQCS_BE_REPORTED'), `pf`.`FIELD_VALUE`, NULL)) AS `AESPQCS_BE_REPORTED`,
    max(if((`pf`.`FIELD_NAME` = 'BLACKBOX_WARNING'), `pf`.`FIELD_VALUE`, NULL)) AS `BLACKBOX_WARNING`,
    max(if((`pf`.`FIELD_NAME` = 'PI_LINK'), `pf`.`FIELD_VALUE`, NULL)) AS `PI_LINK`,
    max(if((`pf`.`FIELD_NAME` = 'FIELD_TEAM_BE_SUBMITTING_MIRS_ON_THIS_PRODUCT'), `pf`.`FIELD_VALUE`, NULL)) AS `FIELD_TEAM_BE_SUBMITTING_MIRS_ON_THIS_PRODUCT`,
    max(if((`pf`.`FIELD_NAME` = 'COMMENTS'), `pf`.`FIELD_VALUE`, NULL)) AS `COMMENTS`,
    max(if((`pf`.`FIELD_NAME` = 'IS_IT_TRADEMARK_OR_REGISTERED'), `pf`.`FIELD_VALUE`, NULL)) AS `IS_IT_TRADEMARK_OR_REGISTERED`,
    max(if((`pf`.`FIELD_NAME` = 'BRAND_STRENGTH'), `pf`.`FIELD_VALUE`, NULL)) AS `BRAND_STRENGTH`,
    max(if((`pf`.`FIELD_NAME` = 'INDICATION'), `pf`.`FIELD_VALUE`, NULL)) AS `INDICATION`,
    max(if((`pf`.`FIELD_NAME` = 'FDA_APPROVED'), `pf`.`FIELD_VALUE`, NULL)) AS `FDA_APPROVED`,
    max(if((`pf`.`FIELD_NAME` = 'QUADRANT'), `pf`.`FIELD_VALUE`, NULL)) AS `QUADRANT`,
    max(if((`pf`.`FIELD_NAME` = 'WEBSITE_PRODUCT_NAME'), `pf`.`FIELD_VALUE`, NULL)) AS `WEBSITE_PRODUCT_NAME`,
    max(if((`pf`.`FIELD_NAME` = 'ANALYTICS_DISPLAY_NAME'), `pf`.`FIELD_VALUE`, NULL)) AS `ANALYTICS_DISPLAY_NAME`
from
    `mdm_field_view` `pf`
where
    (`pf`.`RECORD_ID_2` is not null)
group by
    `pf`.`RECORD_ID_2`;