displaying rows with not null columns excluded-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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