CODEWITHSUNDEEP
mysqlpivot
RachelD
RachelD

Reputation: 4089

MySQL create columns out of rows on the fly

My issue is that I have a table apidata that holds multiple rows of data for each domain. So when I query apidata I naturally get multiple rows as a result. Is there any way to turn those rows into columns? I ask because I'm already using a query to pull the domain data (page title, URL, top level domain, ip address etc) and I need to add the api data with it. I believe I have to do this in two queries but I would love to at least have one row per domain to make the query and loop as fast a possible.

So the question is, can I create columns out of rows on the fly?

Heres a SQL Fiddle => http://sqlfiddle.com/#!2/8e408/4

(Note, I didnt put the whole database in the fiddle just the tables that effect the query. If you think somethings missing that you need, let me know.)

Tool_Runs (id_sha is the main lookup value for tool runs)

| ID | ID_SHA                                   |
+----+------------------------------------------+
| 1  | 68300DF58B2A8A6E098CB0B3D1A9AE80BBE5897A |

Domains (Run_id is FK to tool_runs.id)

| ID | RUN_ID |
+----+--------+
| 1  |   1    |

API Data 

| ID | DOMAIN_ID | EXPORT_COLUMN    | COLUMN_TITLE      | VALUE |
+----+-----------+------------------+-------------------+-------+
| 1  | 1         | referringDomains | Referring Domains | 10    |
+----+-----------+------------------+-------------------+-------+
| 2  | 1         | linkCount        | Backlink Count    | 55    |

Heres my query now:

SELECT a.domain_id, a.export_column, a.column_title, a.value 
FROM apidata AS a WHERE domain_id IN
(
  SELECT d.id FROM tool_runs AS t
  JOIN domains AS d ON d.run_id = t.id
  WHERE id_sha = '68300DF58B2A8A6E098CB0B3D1A9AE80BBE5897A'
)
ORDER BY a.domain_id

And what I get is:

| DOMAIN_ID | EXPORT_COLUMN    | COLUMN_TITLE      | VALUE    |
+-----------+------------------+-------------------+----------+
| 1         | referringDomains | Referring Domains | 10       |
+-----------+------------------+-------------------+----------+
| 1         | linkCount        | Backlink Count    | 55       |

But what I want is 

| DOMAIN_ID | referringDomains | referringDomains_TITLE | linkCount | linkCount_TITLE |
+-----------+------------------+------------------------+-----------+-----------------+
| 1         | 10               | Referring Domains      | 55        | Backlink Count  |

Upvotes: 2

Views: 692

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

What you are trying to is to pivot the table rows into columns. Unfortunately MySQL doesn't have a native pivot table operator, but you can use the CASE expression to do so:

SELECT 
  a.Domain_id,
  MAX(CASE WHEN a.export_column = 'referringDomains' THEN a.value END) AS referringDomains,
  MAX(CASE WHEN a.export_column = 'referringDomains' THEN a.column_title END) AS referringDomains_TITLE,
  MAX(CASE WHEN a.export_column = 'linkCount' THEN a.value END) AS linkCount,
  MAX(CASE WHEN a.export_column = 'linkCount' THEN a.column_title END) AS linkCount_TITLE
FROM apidata AS a 
WHERE domain_id IN
(
  SELECT d.id FROM tool_runs AS t
  JOIN domains AS d ON d.run_id = t.id
  WHERE id_sha = '68300DF58B2A8A6E098CB0B3D1A9AE80BBE5897A'
) 
GROUP BY a.domain_id;

Note that: If you want to do so for all the values in the export_column, you have to write a CASE expression for each value. But you can do that using dynamic sql like this:

SET @ecvalues = NULL;
SET @ectitles = NULL;
SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(a.export_column = ''',
      a.export_column, ''', a.value , NULL)) AS ', '''', a.export_column , '''')
  ) INTO @ecvalues
FROM apidata a;


SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(a.export_column = ''',
      a.export_column, ''', column_title , NULL)) AS ', '''', CONCAT(a.export_column , '_Titles'), '''')
  ) INTO @ectitles
FROM apidata a;


SET @sql = CONCAT('SELECT 
  a.Domain_id, ', @ectitles , ',', @ecvalues, '
    FROM apidata AS a 
WHERE domain_id IN
(
  SELECT d.id FROM tool_runs AS t
  JOIN domains AS d ON d.run_id = t.id
  WHERE id_sha = ''68300DF58B2A8A6E098CB0B3D1A9AE80BBE5897A''
) 
GROUP BY a.domain_id;');

prepare stmt 
FROM @sql;

execute stmt;

You can put that query inside a stored procedure.

Upvotes: 1

AdrianBR
AdrianBR

Reputation: 2588

If you want columns, go ahead and pivot as the example above. If you only want a single string, for some reporting reason, go ahead and do:

SELECT group_concat(CONCAT_WS(' ',a.domain_id, a.value, a.column_title, a.export_column, 'next row string separator'))
FROM apidata AS a WHERE domain_id IN
(
  SELECT d.id FROM tool_runs AS t
  JOIN domains AS d ON d.run_id = t.id
  WHERE id_sha = '68300DF58B2A8A6E098CB0B3D1A9AE80BBE5897A'
)
ORDER BY a.domain_id

Upvotes: 0

Jorge Campos
Jorge Campos

Reputation: 23371

Just as a complement to the @MahmoudGamal answer you should know that for any new registry (EXPORT_COLUMN) you will have to add a new case statement.

So in order to do it dynamic you can create a procedure as described on this post at dba.stackexchange.

How to transpose/convert rows as columns in mysql

It shows how to do it dynamically.

Upvotes: 0

Related Questions