-- DNA_Y-STR_markerslist.sql
-- a crude report of the Y-STR DNA Test fact with markers and values in tab delimited format
-- 2011-06-18 by Tom Holden
-- copy and paste into spreadsheet for sorting
SELECT Person,
 REPLACE(
  REPLACE(
   REPLACE(
    REPLACE(
     REPLACE(DNAtest, '</value>', CAST (X'0D' AS TEXT)),
    '</marker>',''),
   '</name><value>', CAST (X'09' AS TEXT)),
  '<name>', CAST (X'09' AS TEXT)),
 '<markers>',  CAST (X'0D' AS TEXT))
 AS DNAtest  
 FROM 
 (
 SELECT Surname||", "||Given AS Person, details AS DNAtest
 FROM EventTable LEFT JOIN NameTable USING(OwnerID) 
 WHERE NameType=0 AND EventType=901 AND Details LIKE '%<type>Y-STR%'
 );