WITH
  [Kinship]([PersonID], [Relate1], [Relate2], [Flags], [Degrees], [Desc], [Type], [Relationship]) AS(
    SELECT 
           [PersonID], 
           [Relate1], 
           [Relate2], 
           [Flags], 
           ([Relate1] + [Relate2]) AS [Degrees], 
           CASE [Relate1] WHEN 999 THEN CASE [Relate2] WHEN 1 THEN 'self' WHEN 2 THEN 'spouse' WHEN 3 THEN SUBSTR ('father,mother', [Sex] * 7 + 1, 6) || '-in-law' WHEN 4 THEN SUBSTR ('brother,sister', [Sex] * 8 + 1, 7) || '-in-law' WHEN 5 THEN SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) || '-in-law' END WHEN 0 THEN CASE [Relate2] WHEN 0 THEN 'TBD' WHEN 1 THEN SUBSTR ('father,mother', [Sex] * 7 + 1, 6) WHEN 2 THEN 'grand' || SUBSTR ('father,mother', [Sex] * 7 + 1, 6) ELSE ([Relate2] - 2) || ' great-grand' || SUBSTR ('father,mother', [Sex] * 7 + 1, 6) END WHEN 1 THEN CASE [Relate2] WHEN 0 THEN SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) WHEN 1 THEN SUBSTR ('brother,sister', [Sex] * 8 + 1, 7) WHEN 2 THEN SUBSTR ('uncle,aunt', [Sex] * 6 + 1, 5) WHEN 3 THEN 'grand' || SUBSTR ('uncle,aunt', [Sex] * 6 + 1, 5) ELSE ([Relate2] - 3) || ' great-grand' || SUBSTR ('uncle,aunt', [Sex] * 6 + 1, 5) END WHEN 2 THEN CASE [Relate2] WHEN 0 THEN 'grand' || SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) WHEN 1 THEN SUBSTR ('nephew,niece', [SEX] * 7 + 1, 6) WHEN 2 THEN '1 cousin' ELSE '1 cousin ' || ([Relate2] - 2) || 'x removed' END ELSE CASE WHEN [Relate2] = 0 THEN CASE WHEN ([Relate1] - 2) THEN ([Relate1] - 2) || ' ' ELSE '' END || 'great-grand' || SUBSTR ('son,daughter', [Sex] * 4 + 1, 3 + [Sex] * 5) WHEN [Relate2] = 1 THEN CASE [Relate1] WHEN 3 THEN 'grand' || SUBSTR ('nephew,niece', [SEX] * 7 + 1, 6) ELSE CASE WHEN ([Relate1] - 4) THEN ([Relate1] - 4) || ' ' ELSE '' END || 'great-grand' || SUBSTR ('nephew,niece', [SEX] * 7 + 1, 6) END WHEN [Relate1] = [Relate2] THEN ABS ([Relate1] - 1) || ' cousin' ELSE (MIN ([Relate1], [Relate2]) - 1) || ' cousin ' || ABS ([Relate1] - [Relate2]) || 'x removed' END END AS [Desc], 
           CASE WHEN [Flags] = 10 THEN "Spouse of (Unknown Sex)" WHEN [Flags] = 7 THEN "Spouse of Half (Female)" WHEN [Flags] = 6 THEN "Spouse of (Female)" WHEN [Flags] = 3 THEN "Spouse of Half (Male)" WHEN [Flags] = 2 THEN "Spouse of (Male)" WHEN [Flags] = 1 THEN "Half" ELSE "" END AS [Type], 
           CASE 
           WHEN [Relate1] = 0 AND [Relate2] = 1 AND [SEX] = 0 THEN "Father" 
           WHEN [Relate1] = 0 AND [Relate2] = 1 AND [SEX] = 1 THEN "Mother" 
           WHEN [Relate1] = 1 AND [Relate2] = 0 AND [SEX] = 0 THEN "Son" 
           WHEN [Relate1] = 1 AND [Relate2] = 0 AND [SEX] = 1 THEN "Daughter" 
           WHEN [Relate1] = 0 AND [Relate2] > 1 AND [SEX] = 0 THEN "Grandfather" 
           WHEN [Relate1] = 0 AND [Relate2] > 1 AND [SEX] = 1 THEN "Grandmother" 
           WHEN [Relate1] = 2 AND [Relate2] = 0 AND [SEX] = 0 THEN "Grandson" 
           WHEN [Relate1] = 2 AND [Relate2] = 0 AND [SEX] = 1 THEN "Granddaughter" 
           WHEN [Relate1] = 3 AND [Relate2] = 0 AND [SEX] = 0 THEN "Great-Grandson" 
           WHEN [Relate1] = 3 AND [Relate2] = 0 AND [SEX] = 1 THEN "Great-Granddaughter" 
           WHEN [Relate1] = 1 AND [Relate2] = 1 AND [SEX] = 0 THEN "Brother" 
           WHEN [Relate1] = 1 AND [Relate2] = 1 AND [SEX] = 1 THEN "Sister" 
           WHEN [Relate1] = 1 AND [Relate2] > 1 AND [Relate2] < 999   AND [SEX] = 0 THEN "Uncle" 
           WHEN [Relate1] = 1 AND [Relate2] >= 0  AND [Relate2] < 999 AND [SEX] = 1 THEN "Aunt" 
           WHEN ([Relate1] >= 2 AND [Relate1] < 999) AND [Relate2] >= 1 THEN "Cousin" 
           WHEN [Relate1] = 999 THEN "Immediate" 
           ELSE "" 
           END AS [Relationship]
    FROM   [PersonTable]
    WHERE  [Relate1] + [Relate2] > 0
  )
SELECT 
       *, 
       CASE 
	   WHEN [Relationship] LIKE "Grand%" AND [FLAGS] = 0 THEN 1 
	   WHEN [Relationship] LIKE "Grand%" AND ([FLAGS] = 2 OR [FLAGS] = 6) THEN 4 
	   WHEN ([Relationship] LIKE "%Aunt%" OR [Relationship] LIKE "%Uncle%") AND ([FLAGS] = 2  OR [FLAGS] = 6) THEN 9 
	   WHEN ([Relationship] LIKE "%Aunt%" OR [Relationship] LIKE "%Uncle%") AND ([FLAGS] = 3  OR [FLAGS] = 7) THEN 18 
	   WHEN ([Relationship] LIKE "%Aunt%" OR [Relationship] LIKE "%Uncle%") AND ([FLAGS] = 0) THEN 21 
	   WHEN ([Relationship] LIKE "%Aunt%" OR [Relationship] LIKE "%Uncle%") AND ([FLAGS] = 1) THEN 15 
	   WHEN [Relationship] LIKE "Cousin%" AND [FLAGS] = 0 THEN 5 
	   WHEN [Relationship] LIKE "Cousin%" AND [FLAGS] = 1 THEN 16 
	   WHEN [Relationship] LIKE "Cousin%" AND ([FLAGS] = 3 OR [FLAGS] = 7) THEN 17 
	   WHEN [Relationship] LIKE "Cousin%" AND ([FLAGS] = 2 OR [FLAGS] = 6) THEN 2 
	   WHEN [Relationship] = "Father" or [Relationship] = "Mother" or [Relationship] = "Brother" or [Relationship] = "Sister" or [Relationship] = "Son" or [Relationship] = "Daughter" THEN 1 
	   WHEN [Relationship] = "Grandson"   or [Relationship] = "Great-Grandson" or [Relationship] = "Granddaughter" or [Relationship] = "Great-Granddaughter"   THEN 1 
	   WHEN [Relationship] = "Immediate"  THEN 1 
	   WHEN [Relate1] + [Relate2] = 0 THEN 0 
	   ELSE 27 
	   END AS [Color]
FROM   [Kinship]
ORDER  BY [PersonID]