Search code examples
mysqlgroup-concat

Group_Concat in Concat not working with NULL values

I have a table

CREATE TABLE IF NOT EXISTS `dept` (
  `did` int(11) NOT NULL,
  `dname` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `dept` (`did`, `dname`) VALUES
(1, 'Hi'),
(2, NULL),
(3, 'Hello');

Then I have a query

select group_concat(concat(did,"','",dname) separator '),(') as Result from dept

It is producing result as 1','Hi'),('3','Hello

Question: How can I get result from above query as 1','Hi'),('2','NULL'),('3','Hello

It is missing the rows which have NULL values but I need to fetch all

Link for SQL Fiddle Demo of question

UPDATE: If I have more than one or all columns allowing NULL, Is there some way to apply COALESCE once for all or have to apply individually at each Column?

Solution

  • try this, use COALESCE

    .., COALESCE(dname, 'NULL'),..

    making it NULL string visible. SQLFIDDLE DEMO