Group_Concat in Concat not working with NULL values

mysql group-concat
Sami picture Sami · Sep 29, 2012 · Viewed 9.8k times · Source

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?

Answer

John Woo picture John Woo · Sep 29, 2012

try this, use COALESCE

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

making it NULL string visible. SQLFIDDLE DEMO

Related questions

Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like: SELECT hobbies FROM peoples_hobbies WHERE person_id = 5; My Output: shopping fishing coding but instead I just want 1 row, 1 col: Expected Output: shopping, fishing, coding The reason is that I'm selecting multiple values …

Read More
How to use GROUP_CONCAT in a CONCAT in MySQL

If I have a table with the following data in MySQL: id Name Value 1 A 4 1 A 5 1 B 8 2 C 9 how do I get it into the following format? id Column 1 A:4,5,B:8 2 C:9 I think I have to use GROUP_CONCAT. …

Read More
MySQL and GROUP_CONCAT() maximum length

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters. I'm very well aware that I can change the param group_concat_…

Read More