sql - 'Compact' SELECT results in mySQL -
is there anyway 'compact' results of sql query?
the results like:
+----+-----------+----------+-----------+-------------+--------+ | id | firstname | lastname | hobby | job | age | +----+-----------+----------+-----------+-------------+--------+ | 1 | john | doe | (null) | (null) | 30 | | 1 | john | doe | chess | (null) | (null) | | 2 | adam | jackson | (null) | accountant | (null) | | 2 | adam | jackson | (null) | (null) | 55 | | 3 | michael | smith | knitting | (null) | (null) | | 3 | michael | smith | (null) | banker | (null) | +----+-----------+----------+-----------+-------------+--------+ but this:
+----+-----------+----------+-----------+-------------+--------+ | id | firstname | lastname | hobby | job | age | +----+-----------+----------+-----------+-------------+--------+ | 1 | john | doe | chess | (null) | 30 | | 2 | adam | jackson | (null) | accountant | 55 | | 3 | michael | smith | knitting | banker | (null) | +----+-----------+----------+-----------+-------------+--------+ i tried using group by accepts first custom field database each person.
i've setup sql fiddle here: http://sqlfiddle.com/#!9/39563/2
the setup little odd need keep structure due database i'm working on.
you can use max aggregate function group by flatten result this:
select p.id "user id", p.firstname firstname, p.lastname lastname, max(case when cf.fieldname = 'hobby' cfv.value end) "hobby", max(case when cf.fieldname = 'job' cfv.value end) "job", max(case when cf.fieldname = 'age' cfv.value end) "age" customfields cf join customfieldvalues cfv on cfv.fieldid = cf.id join people p on cfv.relid = p.id cf.fieldname in ('hobby', 'job', 'age') group p.id, p.firstname, p.lastname order p.id; also, string literals should enclosed in single-quotes, , can use aliases reduce query text , make more readable.
Comments
Post a Comment