Ufdjrw

How can i split one column/row to multiple columns and same row. The idea is this:

I have a query that looks this:

SELECT
 i.account_id AS account_id,
 c.url_tags AS url_tags,
 CAST(to_char(date_start, 'YYYYMM') AS INT) AS month_id,
 sum(clicks) AS clicks,
 sum(impressions) AS impressions,
 sum(reach) AS reach,
 sum(spend) AS spend,
 sum(total_actions) AS total_actions
 FROM schema.ads_insights AS i
 INNER JOIN schema.ads as a ON a.id=i.ad_id
 INNER JOIN schema.adcreative as c ON c.id=a.creative__id
 WHERE
 EXTRACT(YEAR FROM date_start) = '2018'
 GROUP BY i.account_id, month_id, c.url_tags

This outputs this:(removed some columns)

|----||--------------------------------------------------------------||----|
| ID || url || mo |
|----||--------------------------------------------------------------||----|
| 1 || utm_source=source&utm_medium=medium&utm_term=term || 12 |
|----||--------------------------------------------------------------||----|
|----||--------------------------------------------------------------||----|
| 2 || utm_source=source2&utm_medium=medium2&utm_term=term2 || 43 |
|----||--------------------------------------------------------------||----|

What i would want without any Backend logic. We have a connector from our database to a visualization platform(Google Data Studio) that feeds the data without any form for manipulating option. This is why i want to manipulate the query so it is compatible without any Backend-code.

This is what i want in result of this query:

|----||------------||------------||----------||--------|
| ID || utm_source || utm_medium || utm_term || mo |
|----||------------||------------||----------||--------|
| 1 || source || medium || term || 12 |
|----||------------||------------||----------||--------|
| 2 || source2 || medium2 || term2 || 43 |
|----||------------||------------||----------||--------|

Would this be possible?

use SUBSTRING

select substring(s from 'utm_source=(.*?)(&|$)') as utm_source,
 substring(s from 'utm_medium=(.*?)(&|$)') as utm_medium,
 substring(s from 'utm_term=(.*?)(&|$)') as utm_term
from t;

Demo