Group_Concat in Concat not working with NULL values

Question

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?

I have another example of groupconcat in a concat http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql/13451984#13451984 — Sami, Jan 17 '16 at 21:02

score 9 · Accepted Answer · edited Sep 29 '12 at 15:43

9

try this, use COALESCE

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

making it NULL string visible. SQLFIDDLE DEMO

edited Sep 29 '12 at 15:43

Sami

8,168
9
66
99

answered Sep 29 '12 at 14:45

John Woo

258,903
69
498
492

Please guide if two or all columns can have NULL values then do I have to use `COALESCE` with each column or there is some method to do this once for all? – Sami Sep 29 '12 at 15:12
unfortunately none, you need to individually add `COALESCE` on each column. – John Woo Sep 29 '12 at 15:14

Michael Berkowski · Answer 2 · 2012-09-29T15:08:10.843

2

From the MySQL aggregate function documentation:

Unless otherwise stated, group functions ignore NULL values.

Use COALESCE() to replace the nulls with a string, since they would be eliminated by the aggregate function. For example COALESCE(dbname, 'NULL') will return the string NULL if dbname IS NULL. Its purpose is to return the first non-null of the arguments you give it, and can therefore return a default value.

SELECT
  GROUP_CONCAT(CONCAT(did,"','", COALESCE(dname, 'NULL')) SEPARATOR "'),('") AS Result
FROM dept

edited Sep 29 '12 at 15:08

answered Sep 29 '12 at 14:45

Michael Berkowski

267,341
46
444
390

Your solution is quite well explained and acceptable. Please Just replace separator `'),('` to `"'),('"` as I have done it in question and sqlfiddle demo – Sami Sep 29 '12 at 14:55
@Sami edited above. You can also escape single quotes as in `'\'),(\''` – Michael Berkowski Sep 29 '12 at 15:08

score 0 · Answer 3 · answered Sep 29 '12 at 14:55

0

Hope following query will serve your purpose

SELECT GROUP_CONCAT(
IF(dname IS NOT NULL, CONCAT(did,"','",dname), CONCAT(did,"','NULL")) 
SEPARATOR '),(') AS Result FROM dept

answered Sep 29 '12 at 14:55

jsist

5,223
3
28
43

Group_Concat in Concat not working with NULL values

3 Answers3

Linked