请写sql语句从表一中提取数据，按表二的结构进行显示

帮我写下sql语句

SELECT ORDER_ID ,1 as ORDER_NO,ICD-10-1 as 'ICD-10' ,NAME1 as 'name' ,TYPE1 as 'type' from '病人诊断信息' union all
SELECT ORDER_ID ,2 as ORDER_NO,ICD-10-2 as 'ICD-10' ,NAME2 as 'name' ,TYPE2 as 'type' from '病人诊断信息'

select orderid,1 as orderno,ICD_10_1 as Icd_10,name1 as name,type1 as type from dat_order
UNION ALL
select orderid,1 as orderno,ICD_10_2 as Icd_10,name2 as name,type2 as type from dat_order

SELECT ORDER_ID, ORDER_NO, ICD-10-1 AS ICD-10, NAME1 AS NAME, TYPE1 AS TYPE FROM (SELECT a.,(@rownum1 := @rownum1 + 1 )AS ORDER_NO FROM 病人诊断信息 a,(SELECT @rownum1 := 0) AS tmp) b
UNION ALL
SELECT ORDER_ID, ORDER_NO, ICD-10-2 AS ICD-10, NAME2 AS NAME, TYPE2 AS TYPE FROM (SELECT a.,(@rownum2 := @rownum2 + 1 )AS ORDER_NO FROM 病人诊断信息 a,(SELECT @rownum2 := 0) AS tmp) c
ORDER BY ORDER_NO