Split string row to multiple columns - PostgreSQL

Question

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?

Kaushik Nayak · Accepted Answer · 2019-03-08 11:08:22Z

3

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

answered Mar 8, 2019 at 11:08

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Split string row to multiple columns - PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related