Difference between revisions of "Manual Phenotyping Database"
From Poland Lab Wiki
(→Row-to-Column Query) |
(→Row-to-Column Query) |
||
| Line 10: | Line 10: | ||
===Row-to-Column Query=== | ===Row-to-Column Query=== | ||
| − | FOR QUERY OF REPEATED MEASUREMENTS | + | FOR QUERY OF REPEATED MEASUREMENTS: This query can be used to extract a column format of a repeat measurements (over dates) for a single trait. |
| − | + | ||
| − | This query can be used to extract a column format of a repeat measurements (over dates) for a single trait. | + | |
The query below is an example of how to generate a report of the PCTHEAD (percent heading) trait. | The query below is an example of how to generate a report of the PCTHEAD (percent heading) trait. | ||
Revision as of 15:09, 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
FOR QUERY OF REPEATED MEASUREMENTS: This query can be used to extract a column format of a repeat measurements (over dates) for a single trait.
The query below is an example of how to generate a 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;
