Manual Phenotyping Database

From Poland Lab Wiki
Revision as of 09:17, 5 October 2016 by Mlucas (Talk | contribs)

Jump to: navigation, search

The manual phenotyping database contains phenotypic field data that was collected by hand.

The main tables in the manual phenotyping database are shown in the figure below.


Phenotyping.png

Example Queries

Row-to-Column Query

The query below is an example of how to generate an annual report of the PCTHEAD (percent heading) trait.

SET SESSION group_concat_max_len = 10000;
SET @SQL = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''')
  ) INTO @SQL FROM phenotype WHERE YEAR(phenotype_date) = 2016;
SET @SQL = CONCAT('SELECT  entity_id, ', @SQL, ' 
                  FROM    phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%''
                  GROUP   BY entity_id');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;