Display data in dynamic columns-CodePudding

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.

CodePudding user response：

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;