oracle 列转行list,Oracle中查询 行转列的方法

1

、固定列数的行列转换 如 student subject grade

--

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

student1 语文

80

student1 数学

70

student1 英语

60

student2 语文

90

student2 数学

80

student2 英语

100

…… 转换为 语文 数学 英语 student1

80

70

60

student2

90

80

100

…… 语句如下：

select

student,

sum

(decode(subject,

'

语文

'

, grade,

null

)) "语文",

sum

(decode(subject,

'

数学

'

, grade,

null

)) "数学",

sum

(decode(subject,

'

英语

'

, grade,

null

)) "英语"

from

table

group

by

student;

2

、不定列行列转换 如 c1 c2

--

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

1

我

1

是

1

谁

2

知

2

道

3

不 …… 转换为

1

我是谁

2

知道

3

不 这一类型的转换可以借助于PL

/

SQL来完成，这里给一个例子

CREATE

OR

REPLACE

FUNCTION

get_c2(tmp_c1

NUMBER

)

RETURN

VARCHAR2

IS

Col_c2

VARCHAR2

(

4000

);

BEGIN

FOR

cur

IN

(

SELECT

c2

FROM

t

WHERE

c1

=

tmp_c1) LOOP Col_c2 :

=

Col_c2

||

cur.c2;

END

LOOP; Col_c2 :

=

rtrim

(Col_c2,

1

);

RETURN

Col_c2;

END

;

select

distinct

c1 ,get_c2(c1) cc2

from

table

; 或者不用pl

/

sql，利用分析函数和 CONNECT_BY 实现：

SELECT

c1, SUBSTR (

MAX

(SYS_CONNECT_BY_PATH (c2,

'

;

'

)),

2

) NAME

FROM

(

SELECT

c1, c2, rn, LEAD (rn)

OVER

(PARTITION

BY

c1

ORDER

BY

rn) rn1

FROM

(

SELECT

c1, c2, ROW_NUMBER ()

OVER

(

ORDER

BY

c2) rn

FROM

t)) START

WITH

rn1

IS

NULL

CONNECT

BY

rn1

=

PRIOR rn

GROUP

BY

c1;

3

、列数不固定(交叉表行列转置) 这种是比较麻烦的一种，需要借助pl

/

sql： 原始数据： CLASS1 CALLDATE CALLCOUNT

1

2005

-

08

-

08

40

1

2005

-

08

-

07

6

2

2005

-

08

-

08

77

3

2005

-

08

-

09

33

3

2005

-

08

-

08

9

3

2005

-

08

-

07

21

转置后： CALLDATE CallCount1 CallCount2 CallCount3

--

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

2005

-

08

-

09

0

0

33

2005

-

08

-

08

40

77

9

2005

-

08

-

07

6

0

21

试验如下：

1

). 建立测试表和数据

CREATE

TABLE

t( class1

VARCHAR2

(

2

BYTE), calldate DATE, callcount

INTEGER

);

INSERT

INTO

t(class1, calldate, callcount)

VALUES

(

'

1

'

, TO_DATE (

'

08/08/2005

'

,

'

MM/DD/YYYY

'

),

40

);

INSERT

INTO

t(class1, calldate, callcount)

VALUES

(

'

1

'

, TO_DATE (

'

08/07/2005

'

,

'

MM/DD/YYYY

'

),

6

);

INSERT

INTO

t(class1, calldate, callcount)

VALUES

(

'

2

'

, TO_DATE (

'

08/08/2005

'

,

'

MM/DD/YYYY

'

),

77

);

INSERT

INTO

t(class1, calldate, callcount)

VALUES

(

'

3

'

, TO_DATE (

'

08/09/2005

'

,

'

MM/DD/YYYY

'

),

33

);

INSERT

INTO

t(class1, calldate, callcount)

VALUES

(

'

3

'

, TO_DATE (

'

08/08/2005

'

,

'

MM/DD/YYYY

'

),

9

);

INSERT

INTO

t(class1, calldate, callcount)

VALUES

(

'

3

'

, TO_DATE (

'

08/07/2005

'

,

'

MM/DD/YYYY

'

),

21

);

COMMIT

;

2

). 建立ref cursor准备输出结果集

CREATE

OR

REPLACE

PACKAGE pkg_getrecord

IS

TYPE myrctype

IS

REF

CURSOR

;

END

pkg_getrecord;

/

3

). 建立动态sql交叉表函数，输出结果集

CREATE

OR

REPLACE

FUNCTION

fn_rs

RETURN

pkg_getrecord.myrctype

IS

s

VARCHAR2

(

4000

);

CURSOR

c1

IS

SELECT

'

,sum(case when Class1=

'

||

class1

||

'

then CallCount else 0 end)

'

||

'

"CallCount

'

||

class1

||

'

"

'

c2

FROM

t

GROUP

BY

class1; r1 c1

%

ROWTYPE; list_cursor pkg_getrecord.myrctype;

BEGIN

s :

=

'

select CallDate

'

;

OPEN

c1; LOOP

FETCH

c1

INTO

r1;

EXIT

WHEN

c1

%

NOTFOUND; s :

=

s

||

r1.c2;

END

LOOP;

CLOSE

c1; s :

=

s

||

'

from T group by CallDate order by CallDate desc

'

;

OPEN

list_cursor

FOR

s;

RETURN

list_cursor;

END

fn_rs;

/

4

). 测试在sql plus下执行：

var

results refcursor;

exec

:results :

=

fn_rs;

print

results; CALLDATE CallCount1 CallCount2 CallCount3

--

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

2005

-

08

-

09

0

0

33

2005

-

08

-

08

40

77

9

2005

-

08

-

07

6

0

21