SQL数据表纵横转换

SELECT DISTINCT
    '(select b.risk from rhwl_easy_genes_new_risk b where b.genes_id=a.id and b.disease=''' || b."disease" || ''') as "' || b."disease" || '",' 
FROM
    rhwl_easy_genes_new A,
    rhwl_easy_genes_new_risk b 
WHERE
    b.genes_id = A.ID 
    AND A.NAME IN (
SELECT A
    .NAME 
FROM
    rhwl_easy_genes_new A 
WHERE
    A.active = TRUE 
    AND A.NAME NOT LIKE'CS%' 
    AND A.package_id IN (
SELECT ID 
FROM
    rhwl_genes_base_package 
WHERE
    code IN ( 'FV', 'JE', 'JC' )));

通过去重，会生成列的select语句：

(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='不宁腿综合征') as "不宁腿综合征",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='萘普生') as "萘普生",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='耐力') as "耐力",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='抗雀斑能力') as "抗雀斑能力",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='利培酮') as "利培酮",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='帕金森病') as "帕金森病",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='顺铂') as "顺铂",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='霍奇金淋巴瘤') as "霍奇金淋巴瘤",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='哮喘') as "哮喘",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='酒精性肝硬化') as "酒精性肝硬化"

把上面的语句插入， 生成最终查询：

SELECT 

p.name as "套餐",
a.name as "样本编号",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='不宁腿综合征') as "不宁腿综合征",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='萘普生') as "萘普生",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='耐力') as "耐力",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='抗雀斑能力') as "抗雀斑能力",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='利培酮') as "利培酮",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='帕金森病') as "帕金森病",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='顺铂') as "顺铂",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='霍奇金淋巴瘤') as "霍奇金淋巴瘤",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='哮喘') as "哮喘",
(select b.risk from rhwl_easy_genes_new_risk b where  b.active=True and b.genes_id=a.id and b.disease='酒精性肝硬化') as "酒精性肝硬化"

FROM  

rhwl_easy_genes_new a,
rhwl_genes_base_package p 

where 

a.state in ('report_done', 'done', 'result_done', 'deliver') 

and a.active = True 

and a.name not like 'CS%' 

and a.package_id in (select id from rhwl_genes_base_package where code in ('FV', 'JE', 'JC'))

and p.id = a.package_id

order by p.name, a.name