materialized_stockprop

SELECT crosstab.stock_id,

    crosstab.uniquename,

    crosstab.stock_type_id,

    crosstab.stock_type_name,

    crosstab.organism_id,

    crosstab.block,

    crosstab.col_number,

    crosstab.igd_synonym,

    crosstab."is a control",

    crosstab.location_code,

    crosstab.organization,

    crosstab.plant_index_number,

    crosstab.subplot_index_number,

    crosstab.tissue_sample_index_number,

    crosstab."plot number",

    crosstab.plot_geo_json,

    crosstab.range,

    crosstab.replicate,

    crosstab.row_number,

    crosstab.stock_synonym,

    crosstab."T1",

    crosstab."T2",

    crosstab.transgenic,

    crosstab.variety,

    crosstab.notes,

    crosstab.state,

    crosstab."accession number",

    crosstab."PUI",

    crosstab.donor,

    crosstab."donor institute",

    crosstab."donor PUI",

    crosstab."seed source",

    crosstab."institute code",

    crosstab."institute name",

    crosstab."biological status of accession code",

    crosstab."country of origin",

    crosstab."type of germplasm storage code",

    crosstab."entry number",

    crosstab."acquisition date",

    crosstab.current_count,

    crosstab.current_weight_gram,

    crosstab.crossing_metadata_jsonb,

    crosstab.ploidy_level,

    crosstab.genome_structure,

    crosstab.introgression_parent,

    crosstab.introgression_backcross_parent,

    crosstab.introgression_map_version,

    crosstab.introgression_chromosome,

    crosstab.introgression_start_position_bp,

    crosstab.introgression_end_position_bp,

    crosstab.is_blank,

    crosstab.concentration,

    crosstab.volume,

    crosstab.extraction,

    crosstab.dna_person,

    crosstab.tissue_type,

    crosstab.seedlot_quality,

    crosstab."SelectionMarker",

    crosstab."CloningOrganism",

    crosstab."CassetteName",

    crosstab."Strain",

    crosstab."InherentMarker",

    crosstab."Backbone",

    crosstab."VectorType",

    crosstab."Gene",

    crosstab."Promotors",

    crosstab."Terminators",

    crosstab.ncbi_taxonomy_id

   FROM sgn.crosstab('SELECT stockprop.stock_id, stock.uniquename, stock.type_id, stock_cvterm.name, stock.organism_id, stockprop.type_id, jsonb_object_agg(stockprop.value, stockprop.rank) FROM public.stockprop JOIN public.stock USING(stock_id) JOIN public.cvterm as stock_cvterm ON (stock_cvterm.cvterm_id=stock.type_id) GROUP BY (stockprop.stock_id, stock.uniquename, stock.type_id, stock_cvterm.name, stock.organism_id, stockprop.type_id) ORDER by stockprop.stock_id ASC'::text, 'SELECT type_id FROM (VALUES

    (''76460''),

    (''76517''),

    (''76518''),

    (''76519''),

    (''76520''),

    (''76521''),

    (''77104''),

    (''77576''),

    (''77607''),

    (''76461''),

    (''77604''),

    (''76522''),

    (''76459''),

    (''76523''),

    (''76487''),

    (''76490''),

    (''76491''),

    (''76489''),

    (''77601''),

    (''77600''),

    (''77602''),

    (''77139''),

    (''77140''),

    (''77141''),

    (''77142''),

    (''77143''),

    (''77144''),

    (''77145''),

    (''77146''),

    (''77147''),

    (''77148''),

    (''77149''),

    (''77150''),

    (''77151''),

    (''77580''),

    (''77621''),

    (''77581''),

    (''77593''),

    (''77592''),

    (''77594''),

    (''77595''),

    (''77596''),

    (''77597''),

    (''77598''),

    (''77599''),

    (''77608''),

    (''77609''),

    (''77610''),

    (''77612''),

    (''77611''),

    (''77613''),

    (''78169''),

    (''78279''),

    (''78280''),

    (''78281''),

    (''78283''),

    (''78282''),

    (''78284''),

    (''78288''),

    (''78285''),

    (''78286''),

    (''78287''),

    (''77614'')) AS t (type_id);'::text) crosstab(stock_id integer, uniquename text, stock_type_id integer, stock_type_name text, organism_id integer, block json, col_number jsonb, igd_synonym jsonb, "is a control" jsonb, location_code jsonb, organization jsonb, plant_index_number jsonb, subplot_index_number jsonb, tissue_sample_index_number jsonb, "plot number" jsonb, plot_geo_json jsonb, range jsonb, replicate jsonb, row_number jsonb, stock_synonym jsonb, "T1" jsonb, "T2" jsonb, transgenic jsonb, variety jsonb, notes jsonb, state jsonb, "accession number" jsonb, "PUI" jsonb, donor jsonb, "donor institute" jsonb, "donor PUI" jsonb, "seed source" jsonb, "institute code" jsonb, "institute name" jsonb, "biological status of accession code" jsonb, "country of origin" jsonb, "type of germplasm storage code" jsonb, "entry number" jsonb, "acquisition date" jsonb, current_count jsonb, current_weight_gram jsonb, crossing_metadata_jsonb jsonb, ploidy_level jsonb, genome_structure jsonb, introgression_parent jsonb, introgression_backcross_parent jsonb, introgression_map_version jsonb, introgression_chromosome jsonb, introgression_start_position_bp jsonb, introgression_end_position_bp jsonb, is_blank jsonb, concentration jsonb, volume jsonb, extraction jsonb, dna_person jsonb, tissue_type jsonb, seedlot_quality jsonb, "SelectionMarker" jsonb, "CloningOrganism" jsonb, "CassetteName" jsonb, "Strain" jsonb, "InherentMarker" jsonb, "Backbone" jsonb, "VectorType" jsonb, "Gene" jsonb, "Promotors" jsonb, "Terminators" jsonb, ncbi_taxonomy_id jsonb);