0

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

Community
  • 1
  • 1
IROEGBU
  • 948
  • 16
  • 33

2 Answers2

2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

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
Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
  • sorry, for the confusion I caused. `value` is the column I'm trying to place the constraint on – IROEGBU Oct 22 '14 at 21:06
  • Then change the where statement to `value IS NOT NULL` – Jorge Campos Oct 22 '14 at 21:08
  • Doesn't work (see [fiddle](http://sqlfiddle.com/#!2/2f5692/2/0)) I want to last row in the query to be omitted. – IROEGBU Oct 22 '14 at 21:12
  • I'm testing your query and I find it very odd that it isn't working. it seems that mysql group function is ignoring the `not null` constraint (where clause). And in order to check whether it is a default behavior or not I tested on the other databases and only oracle worked as it should (http://sqlfiddle.com/#!4/8870c/1). made a few changes on the query in my tests but it is the same principle. So we have to ask to the database experts in a new question why is this happening. – Jorge Campos Oct 22 '14 at 21:54
  • @GordonLinoff could you help us here about this? – Jorge Campos Oct 22 '14 at 21:55
  • I find a solution but it seems not too obvious. I will make a question about this discussion. I will edit the answer with the final query and the sql fiddle. – Jorge Campos Oct 22 '14 at 22:05
  • @iroegbu if you, like me, is curious enough to understand why http://stackoverflow.com/questions/26518305/mysql-postgre-oracle-and-sqlserver-ignoring-is-not-null-filter :) – Jorge Campos Oct 22 '14 at 22:37
  • I tried all kinds of funny things in `WHERE` clause... see, accepted answer, pretty clean. – IROEGBU Oct 23 '14 at 08:48
  • @iroegbu Its a nice answer indeed! It was him that clarify the problem. It is absurdly simple. I was thinking about filter the value, but it was the ID that it was beeing grouped that matter. So you have three registries with id 3 and one of then with null value, the where clause was filtering it but when you pivot the table you still have two of then. :) – Jorge Campos Oct 24 '14 at 11:36