I am solving a problem very similar to this only in my case, I am not summing any values.
I have been able to write a select that works using solution from this page
SELECT
id,
GROUP_CONCAT(if(colID = 1, value, NULL)) AS 'First Name',
GROUP_CONCAT(if(colID = 2, value, NULL)) AS 'Last Name',
GROUP_CONCAT(if(colID = 3, value, NULL)) AS 'Job Title'
FROM tbl
GROUP BY id;
However, I want to omit rows that have the value
to be null
I assume you want to drop the result row if any of the source rows has value IS NULL
.
You should be able to achieve that with bit_and() in the HAVING
clause:
SELECT id
, max(CASE WHEN colID = 1 THEN value END) AS fn
, max(CASE WHEN colID = 2 THEN value END) AS ln
, max(CASE WHEN colID = 3 THEN value END) AS jt
FROM tbl
GROUP BY id
HAVING bit_and(value IS NOT NULL);
Alternative:
...
HAVING count(*) = count(value);
I didn't spell out ELSE NULL
in the CASE statements because (per documentation):
If there was no matching result value, the result after ELSE
is returned, or NULL
if there is no ELSE
part.
Just add this constraint to the where statement of your query, like this:
SELECT
id,
GROUP_CONCAT(if(colID = 1, value, NULL)) AS 'First Name',
GROUP_CONCAT(if(colID = 2, value, NULL)) AS 'Last Name',
GROUP_CONCAT(if(colID = 3, value, NULL)) AS 'Job Title'
FROM tbl
WHERE value IS NOT NULL
GROUP BY id;
EDIT
After some tests I could make a solution to work, but it seems interesting why value is not null
won't work.
Solution link: http://sqlfiddle.com/#!2/b7a445/3
SELECT
id,
max(case when colID = 1 then value else '' end) AS fn,
max(case when colID = 2 then value else '' end) AS ln,
max(case when colID = 3 then value else '' end) AS jt
FROM tbl
where not exists (select * from tbl b where tbl.id=b.id and value is null)
group by id