How can I unpivot multiple columns and exclude values from either column?

Question

I have a table that contains an id and two products columns, product and product_other. The product column may have a valid product name, the word "other", or be blank, as it comes from the source. The product_other will be expected to have a valid product name if product is "other", and otherwise may be blank or contain a valid product name randomly. I need to pull all valid product names into a single column and exclude the blanks and "other" entries. I've tried two methods so far, the UNION method is the below:

SELECT DISTINCT * FROM
(
    SELECT id
        , COALESCE(NULLIF(NULLIF(product, 'other'), ''), 'UNKNOWN') AS product
    FROM myTable
    UNION
    SELECT id
        , COALESCE(NULLIF(product_other, ''), 'UNKNOWN') AS product
    FROM myTable
) k
ORDER BY id

This produces all rows, including 'UNKNOWN' values for product and valid product names for product_other for the same id, when it should only provide the valid name if there is one.

The other method I've used is the CASE WHEN method below:

SELECT id
    , CASE COALESCE(NULLIF(product, ''), 'UNKNOWN')
            WHEN 'other' THEN COALESCE(NULLIF(product_other, ''), 'UNKNOWN')
            ELSE COALESCE(NULLIF(product_other, ''), 'UNKNOWN')
        END AS product
FROM myTable
ORDER BY id

This one, however, has only produced a list of valid product where available or product_other when product is "other" or blank. It excludes the possibility of cases where there are valid product product and product_other values.

Another way to look at it is if you SELECT id, count(*) FROM (either query as k ) GROUP BY id, there will be rows having count of 2 in the first case, whether one is "UNKNOWN" or not, and the second will always have only 1 row per id.

So, myTable looks like this:

id      product     product_other
------  ---------   ---------------
155535  OTC         COM
155536              OTC
155537  other       COM
155538  

I want the query results to look like this:

id      product
------  ---------
155535  OTC
155535  COM
155536  OTC
155537  COM
155538  UNKNOWN

Thanks.

Answer 1

Try this:

    CREATE TABLE #temp (id   int ,   product  varchar(10),    product_other VARCHAR(10))


    INSERT INTO #temp 
    VALUES(155535,'OTC','COM'),
    (155536, '' ,'OTC'),
    (155537,'other','COM'),
    (155538,'','') 

SELECT id
    , CASE  WHEN  COALESCE(NULLIF(product, ''),NULLIF(product_other, ''))  = 'other' AND NULLIF(product_other, '') IS NOT NULL  THEN product_other
           WHEN   COALESCE(NULLIF(product, ''),NULLIF(product_other, '')) IS NULL AND  NULLIF(product_other, '') IS NULL  THEN  'UNKNOWN' 
           ELSE  COALESCE(NULLIF(product, ''),NULLIF(product_other, '')) END  AS product 
FROM #temp
UNION
SELECT id
    ,CASE  WHEN  COALESCE(NULLIF(product_other, ''),NULLIF(product, ''))  = 'other' AND NULLIF(product, '') IS NOT NULL  THEN product_other
           WHEN   COALESCE(NULLIF(product_other, ''),NULLIF(product, '')) IS NULL AND  NULLIF(product, '') IS NULL  THEN  'UNKNOWN' 
           ELSE  COALESCE(NULLIF(product_other, ''),NULLIF(product, '')) END  AS product 
FROM #temp;

And the results:

So in essence this is the logic:

SELECT id
    , CASE  WHEN  COALESCE(NULLIF(product, ''),NULLIF(product_other, ''))  = 'other' AND NULLIF(product_other, '') IS NOT NULL  THEN product_other
           WHEN   COALESCE(NULLIF(product, ''),NULLIF(product_other, '')) IS NULL AND  NULLIF(product_other, '') IS NULL  THEN  'UNKNOWN' 
           ELSE  COALESCE(NULLIF(product, ''),NULLIF(product_other, '')) END  AS product 
FROM myTable
UNION
SELECT id
    ,CASE  WHEN  COALESCE(NULLIF(product_other, ''),NULLIF(product, ''))  = 'other' AND NULLIF(product, '') IS NOT NULL  THEN product_other
           WHEN   COALESCE(NULLIF(product_other, ''),NULLIF(product, '')) IS NULL AND  NULLIF(product, '') IS NULL  THEN  'UNKNOWN' 
           ELSE  COALESCE(NULLIF(product_other, ''),NULLIF(product, '')) END  AS product 
FROM myTable;