Content Comparison

--CREATE TABLE bdbsa.analysis_length AS
SELECT "VISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH"
from bdbsa.suspecies s
where ("LENGTHMETHOD" is not null and "LENGTH" is not null);
--drop TABLE if exists bdbsa.analysis_length; not null);
--drop TABLE if exists bdbsa.analysis_length;

select distinct "LENGTHMETHOD"
from bdbsa.analysis_length
ORDER  BY 1;

SELECT concat("VISITNR", '_', "SPECIESTYPE",'_',"SPSEQNR",'_',"NSXCODE")as row_id,"VISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH" 
    FROM   bdbsa.analysis_length
    ORDER  BY 5;
   
   
--CREATE EXTENSION IF NOT EXISTS tablefunc;
--create table bdbsa.analyses_length_wide as   
SELECT *
FROM   crosstab(
   $$SELECT concat("VISITNR", '_', "SPECIESTYPE",'_',"SPSEQNR",'_',"NSXCODE") as row_id,"VISITNR", "SPECIESTYPE","SPSEQNR","NSXCODE","LENGTHMETHOD", "LENGTH" 
    FROM   bdbsa.analysis_length
    ORDER  BY 5$$  
  , $$select distinct "LENGTHMETHOD"
from bdbsa.analysis_length
ORDER  BY 1$$
   ) AS ("row_id" text,"VISITNR" float8, "SPECIESTYPE" text,"SPSEQNR" float8, "NSXCODE" text,
   "total_length" float8,
   "svlength" float8, "tail_length" float8,"disc_length" float8,
   "mantlelength" float8, "carapace_length" float8, "fork" float8,
   "lowerjawfork" float8,"pes_length" float8,"head_length" float8,
   "forearm_length" float8);
--drop TABLE if exists bdbsa.analyses_length_wide;