表A:
col1 col2 x20 d21x29 d30x39
aaa 15 5 5 5
bbb 20 7 8 5
要用一条select语句查出以下结果: col1 col2 items values aaa 15 x20 5
aaa 15 d21x29 5
aaa 15 d30x39 5
bbb 20 x20 7
bbb 20 d21x29 8
bbb 20 d30x39 5
col1 col2 x20 d21x29 d30x39
aaa 15 5 5 5
bbb 20 7 8 5
要用一条select语句查出以下结果: col1 col2 items values aaa 15 x20 5
aaa 15 d21x29 5
aaa 15 d30x39 5
bbb 20 x20 7
bbb 20 d21x29 8
bbb 20 d30x39 5
UNION ALL
SELECT COL1,COL2 ,D21X29 FROM TA
UNION ALL
SELECT COL1,COL2 ,D30X39 FROM TA
UNION ALL
SELECT COL1,COL2 ,'D21X29',D21X29 FROM TA
UNION ALL
SELECT COL1,COL2 ,'D30X39',D30X39 FROM TA
'x20' as items,
x20 as values
from 表A
union all
select col1,col2,
'd21x29' as items,
d21x29 as values
from 表A
union all
select col1,col2,
'd30x39' as items,
d30x39 as values
from 表A
'x20' as items,
x20 as values
from 表A
union all
select col1,col2,
'd21x29' as items,
d21x29 as values
from 表A
union all
select col1,col2,
'd30x39' as items,
d30x39 as values
from 表A
order by col1,col2
union all
select col1 , col2 , items = 'd21x29' , values = d21x29 from a
union all
select col1 , col2 , items = 'd30x39' , values = d30x39 from a
union all
select col1 , col2 , items = 'd21x29' , values = d21x29 from a
union all
select col1 , col2 , items = 'd30x39' , values = d30x39 from a
order by col1