Using Postgresql 9.4 version.
I have to prepare one report which compares table structure across the environment i.e. QA, UAT, PROD. I have loaded the table structure from all env in one table and wants to generate the report using SQL crosstab query.
I am able to compare the column name but not able to extend to the data type.
create table test_schema_rpt
(
env_name varchar2(100),
tbl_name varchar2(100),
col_nm varchar2(100),
data_type varchar2(100),
col_len integer
);
insert into test_schema_rpt values ('PROD','test_tbl','col1','character varying','100');
insert into test_schema_rpt values ('PROD','test_tbl','col2','character varying','200');
insert into test_schema_rpt values ('PROD','test_tbl','col3','character varying','300');
insert into test_schema_rpt values ('PROD','test_tbl','col4','integer',null);
insert into test_schema_rpt values ('UAT','test_tbl','col1','character varying','100');
insert into test_schema_rpt values ('UAT','test_tbl','col2','character varying','300');
insert into test_schema_rpt values ('UAT','test_tbl','col3','character','1');
insert into test_schema_rpt values ('UAT','test_tbl','col4','numeric',null);
insert into test_schema_rpt values ('UAT','test_tbl','col5','text',null);
insert into test_schema_rpt values ('QA','test_tbl','col1','character varying','100');
insert into test_schema_rpt values ('QA','test_tbl','col2','character varying','200');
insert into test_schema_rpt values ('QA','test_tbl','col3','character varying','300');
insert into test_schema_rpt values ('QA','test_tbl','col4','numeric',null);
insert into test_schema_rpt values ('QA','test_tbl','col5','text',null);
insert into test_schema_rpt values ('QA','test_tbl','col6','character varying','500');
Using this query
select * from crosstab
(
'select tbl_name||''.''||col_nm::text table_nm , env_name, col_nm::text
from test_schema_rpt order by 1,2'
,'select distinct env_name from test_schema_rpt order by 1'
)
as tbl
(
table_nm text
,"QA" text
,"UAT" text
,"PROD" text
);
Please see expected result set image