Mysql query to get rows of a table as columns of another, with column names from third table

Question

The real problem I'm trying to solve, is how to get all the clients from WHMCS database with all their custom fields. I thought I could use a query in phpmyadmin in order to finally get the result in a csv.

Short explanation of database scheme structure: WHMCS, has two extra tables (tblCustomFields and tblCustomFieldsValues) to facilitate custom fields created by the application users. For every new custom field created, the name and its id are stored in tblCustomFields. When a custom field in a client's profile is filled, the value is stored with the id of the client and the id of the field name in a row of tblCustomFieldsValues.

Simplified / Generic version of the problem

I'll try to describe a simplified generic version of the question which may apply to many similar situations.

Hypothesis

I have three tables:

+----------------------------------------------------+
|                     tblClients                     |
+----------------------------------------------------+
| id | company   | standard_field1 | standard_field2 |
+----+-----------+-----------------+-----------------+
| 1  | A company | abc             | yz              |
+----+-----------+-----------------+-----------------+
| 2  | B company | xyz             | foo             |
+----+-----------+-----------------+-----------------+
| 3  | C company | zyx             | bar             |
+----+-----------+-----------------+-----------------+


+--------------------------+
|      tblCustomFields     |
+--------------------------+
| id | fieldname           |
+----+---------------------+
| 1  | Custom Field Name 1 |
+----+---------------------+
| 2  | Custom Field Name 2 |
+----+---------------------+
| 3  | Custom Field Name 3 |
+----+---------------------+

+----------------------------------------------------+
|                tblCustomFieldsValues               |
+----------------------------------------------------+
|      relid      |      fieldid       |    value    |
| (corresponds to |   (corresponds to  |             |
|   a client id)  | a custom field id) |             |
+-----------------+--------------------+-------------+
| 1               | 1                  | any value   |
+-----------------+--------------------+-------------+
| 1               | 2                  | some value  |
+-----------------+--------------------+-------------+
| 1               | 3                  | field value |
+-----------------+--------------------+-------------+
| 2               | 1                  | data        |
+-----------------+--------------------+-------------+
| 2               | 2                  | whatever    |
+-----------------+--------------------+-------------+
| 2               | 3                  | anything    |
+-----------------+--------------------+-------------+
| 3               | 1                  | and so on   |
+-----------------+--------------------+-------------+
| 3               | 2                  | and on      |
+-----------------+--------------------+-------------+

Objective

I need the data of all the clients, exported from the database to a csv. Important: Every client should appear in only one line. The expected result should look similar to this:

+-----------------------------------------------------------------------------+
|       query result, clients with their custom fields as extra columns       |
+-----------------------------------------------------------------------------+
| id |  company  | standard | standard |  Custom   |   Custom   |   Custom    |
|    |           |  field1  |  field2  |   Field   |    Field   |    Field    |
|    |           |          |          |   Name 1  |   Name 2   |    Name 3   |
+----+-----------+----------+----------+-----------+------------+-------------+
| 1  | A company | abc      | yz       | any value | some value | field value |
+----+-----------+----------+----------+-----------+------------+-------------+
| 2  | B company | xyz      | foo      | data      | whatever   | anything    |
+----+-----------+----------+----------+-----------+------------+-------------+
| 3  | C company | zyx      | bar      | and so on | and on     |             |
+----+-----------+----------+----------+-----------+------------+-------------+

The idea

I thought that a solution would be to enter a query in phpmyadmin and then easily get the result in a csv.

The final question

What would be the mysql query to get all the clients with their custom fields in separate columns ? Is there a better way to reach the objective stated above?

If you have any ideas on how to rephrase the title or any part of the question in order to make it more clear, please comment!

Naveed Ramzan · Answer 1 · 2016-01-07T12:06:39.227

0

select distinct *
from tblClients tc
join tblCustomFieldsValues tcfv on tcfv.client_id = tc.id
join tblCustomFields tcf on tcfv.custom_field_id = tcf.id

edited Jan 07 '16 at 12:06

answered Jan 07 '16 at 11:09

Naveed Ramzan

3,565
3
25
30

Using the column names from the original question and with a minor edit because of a syntax error, this is the query that I really tried: select distinct * from tblclients tc join tblcustomfieldsvalues tcfv on tcfv.relid = tc.id join tblcustomfields tcf on tcfv.fieldid = tcf.id The above query gets the data but returns multiple lines per client! I'm looking for a way to get all the data in one line per client. Thanks for your time! – user210528 Jan 07 '16 at 12:05
I have updated the query with respect to syntax error. Secondly can you paste here your updated query ... ? – Naveed Ramzan Jan 07 '16 at 12:07
Sorry, I pressed [enter] to early in my previous comment. I added the query that I used. The problem is that it returns multiple lines per client. – user210528 Jan 07 '16 at 12:10
Well, all data of client in one line will not be possible in query .. because you have a table which contains 1toM relation. So for that, you need to fetch rows from result set and make the array with respect to client. In the output each client have a sub array with all custom fields and values. Then you can implode with comma and show data – Naveed Ramzan Jan 07 '16 at 12:13

score 0 · Accepted Answer · answered Jan 07 '16 at 12:25

You can use the Pivot to get the expected output but as Mysql doesn't support pivot you have to use the case when clause. SQL query is as follows: Static SQL Query:

Select TB.*, TC.`standard_field1`, TC.`standard_field2`
from tblClients TC
Inner Join(
SELECT TBD.Id, TBD.company,
  Max(CASE WHEN (TBD.fieldname ='Custom Field Name 1') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 1',
  Max(CASE WHEN (TBD.fieldname ='Custom Field Name 2') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 2',
  Max(CASE WHEN (TBD.fieldname ='Custom Field Name 3') THEN TBD.value ELSE NULL END) AS 'Custom Field Name 3'
FROM(
Select TC.Id, TC.Company,
  TCF.fieldname, TCFV.Value
  from tblCustomFieldsValues TCFV
  Inner Join tblClients TC
  ON TCFV.relid = TC.ID
  Inner Join tblCustomFields TCF
  ON TCFV.fieldid = TCF.Id) TBD

 Group BY TBD.Id, TBD.company) TB
 On TB.Id = TC.Id

I am also providing the SQL fiddle link which will help you to get complete data along with output.

Dynamic SQl Query:

SET @sql = NULL;
SELECT 
  GROUP_CONCAT(DISTINCT
               CONCAT('Max(CASE WHEN (TBD.fieldname =''', UN.fieldname,
                      ''') THEN TBD.value ELSE NULL END) AS ''',UN.fieldname,''''))
    INTO @sql
    FROM (
      Select * from tblCustomFields) UN;

SET @sql =  CONCAT('Select TB.*, TC.`standard_field1`, TC.`standard_field2`
                    from tblClients TC
                    Inner Join(SELECT TBD.Id, TBD.company, ', @sql, ' 
                      FROM(
                        Select TC.Id, TC.Company,
                          TCF.fieldname, TCFV.Value
                          from tblCustomFieldsValues TCFV
                          Inner Join tblClients TC
                          ON TCFV.relid = TC.ID
                          Inner Join tblCustomFields TCF
                          ON TCFV.fieldid = TCF.Id) TBD

                         Group BY TBD.Id, TBD.company) TB On TB.Id = TC.Id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Fiddle Link

I hope this will help you.