pl/sql 行列轉換問題

[susie8449]

向各位高手請教一下如何用select 執行表A,使運行的結果如下：
表：A

name yuwen shuxue yingyu
a1 70 80 90
a2 71 81 91

運行的結構：
name kemu chengji
a1 yuwen 70
a1 shuxue 80
a1 yingyu 90
a2 yuwen 71
a2 shuxue 81
a2 yingyu 91

謝謝！

[horizonlyhw]

mark下
我的做法太麻煩 就是要做個臨時表，插入要查詢出來的信息，然後order by 就行了

[mantisXF]

TRY IT ..

SQL> SELECT * FROM TEST111;

NAME            YUWEN     SHUXUE     YINGYU
---------- ---------- ---------- ----------
A1                 70         80         90
A2                 71         81         91

SQL> 
SQL> SELECT NAME,
  2         DECODE(RN,1,'YUWEN',2,'SHUXUE',3,'YINGYU',NULL) "KEMU",
  3         SUBSTR(','||YUWEN||','||SHUXUE||','||YINGYU||',',
  4                INSTR(','||YUWEN||','||SHUXUE||','||YINGYU||',',',',1,RN)+1,
  5                 INSTR(','||YUWEN||','||SHUXUE||','||YINGYU||',',',',1,RN+1)
  6                 -INSTR(','||YUWEN||','||SHUXUE||','||YINGYU||',',',',1,RN)-1) "XF_COL"
  7    FROM TEST111,
  8         (
  9          SELECT ROWNUM RN
 10            FROM ALL_OBJECTS
 11           WHERE ROWNUM <= (SELECT MAX(LENGTH(TO_CHAR(YUWEN ||','||SHUXUE||','||
 12                                            YINGYU)) -
 13                                       LENGTH(REPLACE(TO_CHAR(YUWEN||','||SHUXUE ||','||
 14                                                              YINGYU),
 15                                                      ',',
 16                                                      '')))
 17                              FROM TEST111) + 1
 18         )AO
 19   WHERE INSTR(','||YUWEN||','||SHUXUE||','||YINGYU||',',',',1,RN) > 0
 20   ORDER BY 1;

NAME       KEMU   XF_COL
---------- ------ --------------------------------------------------------------------------------
A1         YUWEN  70
A1         SHUXUE 80
A1         YINGYU 90
A2         YUWEN  71
A2         SHUXUE 81
A2         YINGYU 91

6 rows selected

SQL> 

[BlueskyWide]

2楼给你做出来了，请楼主结贴吧。

[mantisXF]

INSTR会在下面的字符串中最前面开始找逗号的位置：当RN为1时，找第1个逗号的位置并返回位置；当RN为2时，找第2个逗号的位置并返回位置;当找不到时就返回0.
A: ',' || YUWEN || ',' || SHUXUE || ',' || YINGYU || ','

[Quote=引用 5 楼 susie8449 的回复:]
引用 2 楼 mantisXF 的回复:
TRY IT ..

SQL codeSQL> SELECT * FROM TEST111;

NAME YUWEN SHUXUE YINGYU
---------- ---------- ---------- ----------
A1 70 80 90
A2 71 81 91

SQL>
SQL> SELECT NAME,
2 DECODE(RN,1,'YUWEN',2,'SHUXUE',3,'YINGYU',NULL) "KEMU",
3 SUBSTR(','||YUWEN||','||SHUXUE|…
[/Quote]

[you_tube]

用decode最好了

[susie8449]

谢谢！现在会照着葫芦画瓢了！:)

[oracledbalgtu]

union简单的就实现了!

CREATE TABLE  a(NAME VARCHAR2(10),yuwen INT,shuxue INT,yingyu INT);
INSERT INTO a VALUES('a1',70,80,90);
INSERT INTO a VALUES('a2',71,81,91);
SELECT NAME, 'yuwen' KEMU, YUWEN CHENGJI
  FROM A
UNION
SELECT NAME, 'shuxue' KEMU, SHUXUE
  FROM A
UNION
SELECT NAME, 'yingyu' KEMU, YINGYU FROM A

输出:
NAME	KEMU	CHENGJI
a1	shuxue	80
a1	yingyu	90
a1	yuwen	70
a2	shuxue	81
a2	yingyu	91
a2	yuwen	71

[Quote=引用楼主 susie8449 的帖子:]
向各位高手請教一下如何用select 執行表A,使運行的結果如下：
表：A

name yuwen shuxue yingyu
a1 70 80 90
a2 71 81 91

運行的結構：
name kemu chengji
a1 yuwen 70
a1 shuxue 80
a1 yingyu 90
a2 yuwen 71
a2 shuxue 81
a2 yingyu 91

謝謝！
[/Quote]

[susie8449]

[Quote=引用 2 楼 mantisXF 的回复:]
TRY IT ..

SQL codeSQL> SELECT * FROM TEST111;

NAME YUWEN SHUXUE YINGYU
---------- ---------- ---------- ----------
A1 70 80 90
A2 71 81 91

SQL>
SQL> SELECT NAME,
2 DECODE(RN,1,'YUWEN',2,'SHUXUE',3,'YINGYU',NULL) "KEMU",
3 SUBSTR(','||YUWEN||','||SHUXUE||','||YINGYU||',',
4 …
[/Quote]

謝謝枫の叶，但是我有些地方不太明白：主要是求子字符串喝查找字符串，我看不明白是怎么回事？
INSTR(',' || YUWEN || ',' || SHUXUE || ',' || YINGYU || ',',
',',
1,
RN)

我剛查過instr的用法：InStr([start, ]string1, string2[, compare]) 返回指定一字符串在另一字符串中最先出现的位置。但是上面的語句我就不明白！希望枫の叶能幫解釋一下。謝謝

[hyrongg]

建议下载我上传的资源，《Oracle 行列转换总结》写得很详细

http://hyrongg.download.csdn.net/

[simonezhlx]

2楼,4楼殊途同归

[c01406]

倒过来会查不？哪位会啊？？？？
知道下面 查上面！

在不知道有什么kemu的情况下