Difference between revisions of "Manual Phenotyping Database"
From Poland Lab Wiki
| Line 11: | Line 11: | ||
The query below is an example of how to generate a monthly report of the PCTHEAD (percent heading) trait. | The query below is an example of how to generate a monthly report of the PCTHEAD (percent heading) trait. | ||
| − | |||
| − | |||
SET SESSION group_concat_max_len = 10000; | SET SESSION group_concat_max_len = 10000; | ||
| Line 20: | Line 18: | ||
CONCAT( | CONCAT( | ||
'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''') | 'MAX(IF(phenotype_date = ''', `phenotype_date`, ''',phenotype_value,NULL)) AS ''', `phenotype_date`,'''') | ||
| − | ) INTO @sql FROM phenotype WHERE YEAR(phenotype_date) = 2016; | + | ) INTO @sql |
| + | FROM phenotype WHERE YEAR(phenotype_date) = 2016; | ||
SET @sql = CONCAT('SELECT entity_id, ', @sql, ' | SET @sql = CONCAT('SELECT entity_id, ', @sql, ' | ||
FROM phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%'' | FROM phenotype WHERE phenotype.entity_id LIKE ''%16ASH0%'' AND phenotype.trait_id LIKE ''%PCTHEAD%'' | ||
| Line 27: | Line 26: | ||
EXECUTE stmt; | EXECUTE stmt; | ||
DEALLOCATE PREPARE stmt; | DEALLOCATE PREPARE stmt; | ||
| − | |||
| − | |||
Revision as of 08:52, 5 October 2016
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.
Example Queries
Row-to-Column Query
The query below is an example of how to generate a monthly 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;
