×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@Florida

来了

newkid(newkid)
本文发表在 rolia.net 枫下论坛CREATE TABLE test(A NUMBER, B NUMBER, C NUMBER);

INSERT INTO test VALUES (2,1,2);
INSERT INTO test VALUES (2,4,5);
INSERT INTO test VALUES (4,3,8);
INSERT INTO test VALUES (4,2,6);
INSERT INTO test VALUES (7,1,1);
INSERT INTO test VALUES (7,2,2);
INSERT INTO test VALUES (7,3,3);
INSERT INTO test VALUES (7,4,4);

COMMIT;

CREATE OR REPLACE TYPE t_row AS OBJECT (
r_num NUMBER
,text VARCHAR2(2000)
)
/


CREATE OR REPLACE TYPE t_matrix AS TABLE OF t_row
/


CREATE OR REPLACE FUNCTION f_matrix RETURN t_matrix
AS
lv_data t_matrix := t_matrix();
lv_text VARCHAR2(2000);
lv_lastrow NUMBER;
lv_lastcol NUMBER;
BEGIN
lv_lastrow := 0;
lv_lastcol := 1;
lv_text := NULL;
FOR lv_rec IN (SELECT * FROM test ORDER BY a,b)
LOOP
IF lv_lastrow <> lv_rec.a THEN

FOR i IN GREATEST(1,lv_lastrow) .. lv_rec.a - 1
LOOP
lv_data.EXTEND;
lv_data(i) := t_row(i,NULL);
END LOOP;

IF lv_lastrow>0 THEN
lv_data(lv_lastrow).text := lv_text;
END IF;

lv_lastrow := lv_rec.a;
lv_text := NULL;
lv_lastcol := 1;
END IF;

lv_text := lv_text||SUBSTR(RPAD('*',lv_rec.b - lv_lastcol+1,CHR(9)),2)||lv_rec.c;
lv_lastcol := lv_rec.b;
END LOOP;

IF lv_lastrow>0 THEN
lv_data.EXTEND;
lv_data(lv_lastrow) := t_row(lv_lastrow,lv_text);
END IF;

RETURN lv_data;
END f_matrix;
/

最后，到TOAD里面执行这么一个查询：
SELECT text FROM TABLE(CAST(f_matrix AS t_matrix)) order by r_num;

结果集是一个包含了TAB分隔符的字串。选择 SAVE AS -> DELIMITED TEXT 到 CLIPBOARD, 然后到一个空白的 EXCEL SHEET 里面粘贴。更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#3371153@0)
Last Updated: 2006-12-11
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 请教个SQL或Excel的问题... -yehongwei(天拖南); 2006-12-8 {763} (#3366186@0)
    Oracle database，用户只有Select的权限，一个表有3列，分别是A,B,C，全部是字符型，所有的行唯一（可以想象成A+B+C=Primary Key），结果是一个表，A是行，B是列，C是表里的值。举例来说，原先的表里有一行，3个列的数值分别是A1, B1, C1，在结果表中，A1行B1列的值是C1

    要求用Toad执行SQL or PL/SQL，结果拷或存到Excel。

    俺能想到的可能的办法是（优先级有高到低）：
    1.写个SQL，直接在Toad里运行
    2.写个PL/SQL，直接在Toad里运行。问题是，1. 俺不确定PL/SQL是否能直接在Toad里执行，并将结果保存到Excel。2.俺也不会PL/SQL，要是哪位大侠抽空写个模版出来，俺照猫画虎一下，心中定然万分感谢。
    3.把原先的表先存到Excel，再有个简单的办法处理一下。PivotTable俺试过了，不成，C不是数值，不能用Min or Max etc. 用Macro or VBA，也不必了，这个俺会，还是懒得写，好长时间不用了，忘的差不多了。

    谢谢先
    • 为什么不用VBA?这是很好的解决办法啊。先在TOAD中SELECT * FROM...然后SAVE AS delimited text to clipboard, 到 EXCEL 里PASTE, 再运行宏把数据填充到另一个sheet -newkid(newkid); 2006-12-8 (#3366350@0)
      • 俺琢磨着应该还有更好的办法，这个表要经常更新，哪位高人要是能写个SQL或PL/SQL，一下子出结果，俺也跟着学点儿新东西不是的。 -yehongwei(天拖南); 2006-12-11 (#3370920@0)
        • PL/SQL办法是有的，得写个函数，等会有空了我做一下 -newkid(newkid); 2006-12-11 (#3371013@0)
          • 来了 -newkid(newkid); 2006-12-11 {1825} (#3371153@0)
            本文发表在 rolia.net 枫下论坛CREATE TABLE test(A NUMBER, B NUMBER, C NUMBER);

            INSERT INTO test VALUES (2,1,2);
            INSERT INTO test VALUES (2,4,5);
            INSERT INTO test VALUES (4,3,8);
            INSERT INTO test VALUES (4,2,6);
            INSERT INTO test VALUES (7,1,1);
            INSERT INTO test VALUES (7,2,2);
            INSERT INTO test VALUES (7,3,3);
            INSERT INTO test VALUES (7,4,4);

            COMMIT;

            CREATE OR REPLACE TYPE t_row AS OBJECT (
            r_num NUMBER
            ,text VARCHAR2(2000)
            )
            /


            CREATE OR REPLACE TYPE t_matrix AS TABLE OF t_row
            /


            CREATE OR REPLACE FUNCTION f_matrix RETURN t_matrix
            AS
            lv_data t_matrix := t_matrix();
            lv_text VARCHAR2(2000);
            lv_lastrow NUMBER;
            lv_lastcol NUMBER;
            BEGIN
            lv_lastrow := 0;
            lv_lastcol := 1;
            lv_text := NULL;
            FOR lv_rec IN (SELECT * FROM test ORDER BY a,b)
            LOOP
            IF lv_lastrow <> lv_rec.a THEN

            FOR i IN GREATEST(1,lv_lastrow) .. lv_rec.a - 1
            LOOP
            lv_data.EXTEND;
            lv_data(i) := t_row(i,NULL);
            END LOOP;

            IF lv_lastrow>0 THEN
            lv_data(lv_lastrow).text := lv_text;
            END IF;

            lv_lastrow := lv_rec.a;
            lv_text := NULL;
            lv_lastcol := 1;
            END IF;

            lv_text := lv_text||SUBSTR(RPAD('*',lv_rec.b - lv_lastcol+1,CHR(9)),2)||lv_rec.c;
            lv_lastcol := lv_rec.b;
            END LOOP;

            IF lv_lastrow>0 THEN
            lv_data.EXTEND;
            lv_data(lv_lastrow) := t_row(lv_lastrow,lv_text);
            END IF;

            RETURN lv_data;
            END f_matrix;
            /

            最后，到TOAD里面执行这么一个查询：
            SELECT text FROM TABLE(CAST(f_matrix AS t_matrix)) order by r_num;

            结果集是一个包含了TAB分隔符的字串。选择 SAVE AS -> DELIMITED TEXT 到 CLIPBOARD, 然后到一个空白的 EXCEL SHEET 里面粘贴。更多精彩文章及讨论，请光临枫下论坛 rolia.net
            • 谢谢指教... -yehongwei(天拖南); 2006-12-12 {4083} (#3372697@0)
              本文发表在 rolia.net 枫下论坛不过俺的限制条件太多了，只有Select的权限，俺的电脑也不能直接连到Server上，否则在俺电脑的Oracle Database里建个DB Link，运行几个SQL就搞定了。

              最后，俺写了个复杂的SQL，把每个B做成一张表（B数量有限），再将它们连起来，运行起来也还不算太慢，300+记录用了20+秒，一天运行几次还可以接受，但是结果一步到位，不用再处理了。

              SELECT frt.responsibility_name "Responsibility",
              sob.profile_option_value "GL Set of Books Name",
              mrc_sob.profile_option_value "MRC: Reporting Set Of Books",
              mo_ou.profile_option_value "MO: Operating Unit",
              mo_dou.profile_option_value "MO: Default Operating Unit",
              hr_user_type.profile_option_value "HR:User Type",
              project_update.profile_option_value "PA: Cross Project -- Update",
              project_view.profile_option_value "PA: Cross Project -- View"
              FROM apps.fnd_responsibility_tl frt,
              (SELECT level_value responsibility_id, profile_option_value
              FROM apps.fnd_profile_option_values
              WHERE level_id = 10003 AND profile_option_id = 1202) sob,
              (SELECT fpov.level_value responsibility_id,
              gsob.NAME profile_option_value
              FROM apps.fnd_profile_option_values fpov,
              apps.gl_sets_of_books gsob
              WHERE fpov.level_id = 10003
              AND fpov.level_value = 57202
              AND fpov.profile_option_id = 2351
              AND fpov.profile_option_value = gsob.set_of_books_id) mrc_sob,
              (SELECT fpov.level_value responsibility_id,
              hou.NAME profile_option_value
              FROM apps.fnd_profile_option_values fpov,
              apps.hr_organization_units_v hou
              WHERE fpov.level_id = 10003
              AND fpov.profile_option_id = 1991
              AND fpov.profile_option_value = hou.organization_id) mo_ou,
              (SELECT fpov.level_value responsibility_id,
              hou.NAME profile_option_value
              FROM apps.fnd_profile_option_values fpov,
              apps.hr_organization_units_v hou
              WHERE fpov.level_id = 10003
              AND fpov.profile_option_id = 5799
              AND fpov.profile_option_value = hou.organization_id) mo_dou,
              (SELECT level_value responsibility_id,
              DECODE (profile_option_value,
              'PER', 'HR User',
              'INT', 'HR with Payroll User',
              'PAY', 'Payroll User',
              ''
              ) profile_option_value
              FROM apps.fnd_profile_option_values
              WHERE level_id = 10003 AND profile_option_id = 1207) hr_user_type,
              (SELECT fpov.level_value responsibility_id,
              DECODE (fpov.profile_option_value,
              'Y', 'Yes',
              'N', 'No',
              ''
              ) profile_option_value
              FROM apps.fnd_profile_option_values fpov
              WHERE fpov.level_id = 10003 AND fpov.profile_option_id = 5) project_update,
              (SELECT fpov.level_value responsibility_id,
              DECODE (fpov.profile_option_value,
              'Y', 'Yes',
              'N', 'No',
              ''
              ) profile_option_value
              FROM apps.fnd_profile_option_values fpov
              WHERE fpov.level_id = 10003 AND fpov.profile_option_id = 4983) project_view
              WHERE frt.LANGUAGE = 'US'
              AND frt.creation_date >= '01-NOV-2006'
              AND frt.responsibility_id = sob.responsibility_id(+)
              AND frt.responsibility_id = mrc_sob.responsibility_id(+)
              AND frt.responsibility_id = hr_user_type.responsibility_id(+)
              AND frt.responsibility_id = mo_ou.responsibility_id(+)
              AND frt.responsibility_id = mo_dou.responsibility_id(+)
              AND frt.responsibility_id = project_update.responsibility_id(+)
              AND frt.responsibility_id = project_view.responsibility_id(+)
              ORDER BY frt.responsibility_name更多精彩文章及讨论，请光临枫下论坛 rolia.net
              • 运行TOAD的机器不是你装本地数据库的那台？你的实际需求和你的问题差距很大，首先C的值是要经过复杂的变换得来的；B的数量既然那么有限，就没必要挖空心思做那么通用的程序。 -newkid(newkid); 2006-12-12 (#3373020@0)
                • 俺是通过Remote Desktop and Internet连到客户的Virtual Machine.Virtual Machine上装了TOAD并和Server在一个局域网内，运行完SQL，结果存到剪贴板，再贴到本地Excel。一上来，俺只是举个例子，没指望做通用的程序。很多东西也是逐渐发现的，如每个C都需要不同的转换。 -yehongwei(天拖南); 2006-12-12 (#3373328@0)

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  专业技术讨论