oracle 10g sql语句

select

msgid,

decode(rn, 1, 'ML', 2, 'MH', 3, 'T10', 4, 'T30', 5, 'T60', 6, 'T90') ch,

decode(rn, 1, ML, 2, MH, 3, T10, 4, T30, 5, T60, 6, T90) value,

createdate

from tb, (select rownum rn from dual connect by rownum<=6) n;

DROP TABLE #tmp

CREATE TABLE #tmp

(

	MSGID INT ,

	ML DECIMAL(5,2),

	MH DECIMAL(5,2),

	T10 DECIMAL(5,2),

	T30 DECIMAL(5,2) ,

	T60 DECIMAL(5,2),

	T90 DECIMAL(5,2),

	CREATEDATE DECIMAL

)

INSERT INTO #tmp

SELECT 10001, 2.23, 4.58, 150 ,230, 340, 220, 20101002093456



INSERT INTO #tmp

SELECT 10002, 1.23 ,4.12, 120, 124, 235 ,246 ,20101002102347



---SELECT * FROM #tmp

---SQL SERVER 2008

SELECT MSGID,CH,VALUE, CREATEDATE

FROM #TMP

UNPIVOT(VALUE FOR CH IN([ML],[MH],[T10],[T30],[T60],[T90]))UNPVT



MSGID       CH                                                                                                                               VALUE                                   CREATEDATE

----------- ----------------------------------------------

10001       ML                                                                                                                               2.23                                    20101002093456

10001       MH                                                                                                                               4.58                                    20101002093456

10001       T10                                                                                                                              150.00                                  20101002093456

10001       T30                                                                                                                              230.00                                  20101002093456

10001       T60                                                                                                                              340.00                                  20101002093456

10001       T90                                                                                                                              220.00                                  20101002093456

10002       ML                                                                                                                               1.23                                    20101002102347

10002       MH                                                                                                                               4.12                                    20101002102347

10002       T10                                                                                                                              120.00                                  20101002102347

10002       T30                                                                                                                              124.00                                  20101002102347

10002       T60                                                                                                                              235.00                                  20101002102347

10002       T90                                                                                                                              246.00                                  20101002102347



(12 row(s) affected)

--这是SQL Server 2005里的方法，不知道Oracle里面能用不！试试吧..

use test

go

if object_id('test.dbo.tb') is not null drop table tb

-- 创建数据表

create table tb

(

MSGID int,

ML float,

MH float,

T10 float,

T30 float,

T60 float,

T90 float,

CREATEDATE char(16)

)

go

--插入测试数据

insert into tb select 10001,2.23,4.58,150,230,340,220,'20101002093456'

union all select 10002,1.23,4.12,120,124,235,246,'20101002102347'

go

--代码实现



select MSGID,CH,VALUE,CREATEDATE from tb unpivot(VALUE for CH in([ML],[MH],[T10],[T30],[T60],[T90]))upt



/*测试结果



MSGID CH VALUE CREATEDATE

----------------------------------

10001	ML	2.23	20101002093456  

10001	MH	4.58	20101002093456  

10001	T10	150	20101002093456  

10001	T30	230	20101002093456  

10001	T60	340	20101002093456  

10001	T90	220	20101002093456  

10002	ML	1.23	20101002102347  

10002	MH	4.12	20101002102347  

10002	T10	120	20101002102347  

10002	T30	124	20101002102347  

10002	T60	235	20101002102347  

10002	T90	246	20101002102347  



(12 行受影响)

*/