Question 113244 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – displaying rows with not null columns excluded

I have a MySQL table named products with default NULL value for size, weight, dimensions
I need to fetch it to get rows with non NULL values the desired result would be like for first row
:

SKU name price size

1 ,product1 ,2.00 , 2

and the second row

SKU name price weight

2 ,product2 ,3.00 , 3

I tried COALESCE but I need also to get the corresponding column name for the non NULL value

SKU   name   price   size   weight    dimensions 

1   product1  2.00    2      NULL       NULL
2   product2  3.00    NULL   3          NULL
3   product3  4.00    NULL   NULL       2x11x22
Tags:

2

Answers


  1. 0

    Use CONCAT() to combine the column name with the column value. If the value is NULL, the concatenation will also be NULL, and CONCAT_WS() will ignore that argument.

    CONCAT_WS(', ',
    CONCAT('size = ', size),
    CONCAT('weight = ', weight),
    CONCAT('dimensions = ', dimensions)
) AS attributes

    CONCAT_WS() will ignore any null values in the arguments, so you won’t get ,, in the result.

    Login or Signup to reply.
  2. 0

    A solution using COALESCE.

    COALESCE returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

    SELECT
   SKU,
   name,
   price,
   COALESCE(size,weight,dimensions) as extraInfo
FROM mytable;

    see: DBFIDDLE

    output:

    SKU name price extraInfo
    1 product1 2.00 2
    2 product2 3.00 3
    3 product3 4.00 2x11x22
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search