SELECT DISTINCT * EXCEPT(CMS_STATUS),
    CASE
    WHEN CMS_STATUS LIKE "%Published in another Document%" THEN "Published in another Document"
    WHEN CMS_STATUS LIKE "%Published%" THEN "Published"
    WHEN CMS_STATUS LIKE "%Product out of stock%" THEN "Product out of stock"
    WHEN OFF_CMS = "True" THEN "Off CMS"
    WHEN CMS_STATUS LIKE "%Document not active%" THEN "Document not active"
    ELSE "Offer or Product information missing"
    END AS CMS_STATUS
FROM
    (SELECT
        Country AS PAYS,
        CONCAT(Codedoc, Yyear) AS SUPPORT_DE_VENTE,
        Productid AS PRODUCT_ID,
        CMS_On_Off AS OFF_CMS,
        Productreasonnotpublished AS CMS_STATUS
    FROM
        `datapipeline-redoute.CMS.OFFRES_DAY`)