Display data in dynamic columns

I have data like this in the postgres db column with two rows named Data_Column,

sample data data

I would like to display the data like this,The colon left hand side is the column name and the colon right hand side is the column value.

Output Format required

I have tried crosstab function ,but it is not working for me.Can u help on this.

>Solution :

This can be achieved using substring() with a regular expression:

select substring(data_column from 'data_as_of_date:([0-9]+)') as data_as_of_date,
       substring(data_column from 'unique_cc:([0-9]+)') as unique_cc
from the_table;       

Leave a Reply