ID Test_Name Value1 Value2 Value3 Vlaue4 <--字段名
1 S/N[39Code_Scan] JA1 JA2 JA3 JA4
1 END PASS PASS PASS PASS
2 S/N[39Code_Scan] JB1 JB2 JB3 JB4
2 END PASS PASS PASS PASS
3 S/N[39Code_Scan] JC1 JC2
3 END PASS PASS ABORT ABORT
1 S/N[39Code_Scan] JA1 PASS
1 S/N[39Code_Scan] JA2 PASS
1 S/N[39Code_Scan] JA3 PASS
1 S/N[39Code_Scan] JA4 PASS
2 S/N[39Code_Scan] JB1 PASS
2 S/N[39Code_Scan] JB2 PASS
2 S/N[39Code_Scan] JB3 PASS
2 S/N[39Code_Scan] JB4 PASS
3 S/N[39Code_Scan] JC1 PASS
3 S/N[39Code_Scan] JC2 PASS
3 S/N[39Code_Scan] ABORT
3 S/N[39Code_Scan] ABORT
1 S/N[39Code_Scan] JA1 JA2 JA3 JA4
1 END PASS PASS PASS PASS
2 S/N[39Code_Scan] JB1 JB2 JB3 JB4
2 END PASS PASS PASS PASS
3 S/N[39Code_Scan] JC1 JC2
3 END PASS PASS ABORT ABORT
1 S/N[39Code_Scan] JA1 PASS
1 S/N[39Code_Scan] JA2 PASS
1 S/N[39Code_Scan] JA3 PASS
1 S/N[39Code_Scan] JA4 PASS
2 S/N[39Code_Scan] JB1 PASS
2 S/N[39Code_Scan] JB2 PASS
2 S/N[39Code_Scan] JB3 PASS
2 S/N[39Code_Scan] JB4 PASS
3 S/N[39Code_Scan] JC1 PASS
3 S/N[39Code_Scan] JC2 PASS
3 S/N[39Code_Scan] ABORT
3 S/N[39Code_Scan] ABORT
a.ID as ID,
a.Test_Name as Test_Name,
a.Value1 as Type,
b.Value1 as Status
from
(select * from Test_Name where Test_Name != 'END') a
inner join
(select * from Test_Name where Test_Name = 'END') b
on
a.ID = b.ID
UNION ALL
select
a.ID
a.Test_Name,
a.Value2,
b.Value2
from
(select * from Test_Name where Test_Name != 'END') a
inner join
(select * from Test_Name where Test_Name = 'END') b
on
a.ID = b.ID
UNION ALL
select
a.ID
a.Test_Name,
a.Value3,
b.Value3
from
(select * from Test_Name where Test_Name != 'END') a
inner join
(select * from Test_Name where Test_Name = 'END') b
on
a.ID = b.ID
UNION ALL
select
a.ID
a.Test_Name,
a.Value4,
b.Value4
from
(select * from Test_Name where Test_Name != 'END') a
inner join
(select * from Test_Name where Test_Name = 'END') b
on
a.ID = b.ID
order by
a.ID,
a.Test_Name
select id,Test_Name,
(select value1 from table1 where id=a.id and Test_Name =' END') v1,
(select value2 from table1 where id=a.id and Test_Name =' END') v2,
(select value3 from table1 where id=a.id and Test_Name =' END') v3,
(select vlaue4 from table1 where id=a.id and Test_Name =' END') v4
from table1 a
where Test_Name <>' END'select id,test_name,,value1,v1 from tmp_table1
union all
select id,test_name,value2,v2 from tmp_table1
union all
select id,test_name,value3,v3 from tmp_table1
union all
select id,test_name,vlaue4,v4 from tmp_table1
order by id
union all
select id,test_name,value2,v2 from tmp_table1
union all
select id,test_name,value3,v3 from tmp_table1
union all
select id,test_name,vlaue4,v4 from tmp_table1
order by id
SELECT Tab1.Id || Tab1.Test_Name || Tab1.VALUE || Tab2.VALUE
FROM (SELECT Id, Test_Name, 1 AS Val, Value1 AS VALUE
FROM t_Test
WHERE Test_Name <> 'END'
UNION ALL
SELECT Id, Test_Name, 2 AS Val, Value2 AS VALUE
FROM t_Test
WHERE Test_Name <> 'END'
UNION ALL
SELECT Id, Test_Name, 3 AS Val, Value3 AS VALUE
FROM t_Test
WHERE Test_Name <> 'END'
UNION ALL
SELECT Id, Test_Name, 4 AS Val, Value4 AS VALUE
FROM t_Test
WHERE Test_Name <> 'END') Tab1,
(SELECT Id, 1 AS Val, Value1 AS VALUE
FROM t_Test
WHERE Test_Name = 'END'
UNION ALL
SELECT Id, 2 AS Val, Value2 AS VALUE
FROM t_Test
WHERE Test_Name = 'END'
UNION ALL
SELECT Id, 3 AS Val, Value3 AS VALUE
FROM t_Test
WHERE Test_Name = 'END'
UNION ALL
SELECT Id, 4 AS Val, Value4 AS VALUE
FROM t_Test
WHERE Test_Name = 'END') Tab2
WHERE Tab1.Id = Tab2.Id
AND Tab1.Val = Tab2.Val
Insert into tb
select '1','S/N[39Code_Scan]','JA1','JA2','JA3','JA4'
union all select '1','END','PASS','PASS','PASS','PASS'
union all select '2','S/N[39Code_Scan]','JB1','JB2','JB3','JB4'
union all select '2','END','PASS','PASS','PASS','PASS'
union all select '3','S/N[39Code_Scan]','JC1','JC2',null,null
union all select '3','END','PASS','PASS','ABORT','ABORT'select * from tb--查詢結果:
select distinct *,item=case when value1 is null then 'ABORT' else 'PASS' end from
(select distinct id,test_name,value1 from tb where test_name<>'END'
union all
select distinct id,test_name,value2 from tb where test_name<>'END'
union all
select distinct id,test_name,value3 from tb where test_name<>'END'
union all
select distinct id,test_name,value4 from tb where test_name<>'END'
)a
ORDER BY 1,3id test_name value1 item
-----------------------------------------
1 S/N[39Code_Scan] JA1 PASS
1 S/N[39Code_Scan] JA2 PASS
1 S/N[39Code_Scan] JA3 PASS
1 S/N[39Code_Scan] JA4 PASS
2 S/N[39Code_Scan] JB1 PASS
2 S/N[39Code_Scan] JB2 PASS
2 S/N[39Code_Scan] JB3 PASS
2 S/N[39Code_Scan] JB4 PASS
3 S/N[39Code_Scan] NULL ABORT
3 S/N[39Code_Scan] JC1 PASS
3 S/N[39Code_Scan] JC2 PASS