oracle行转列（动态行转不定列)

小蒜头 posted @ 2013年4月07日 20:35 in [Oracle数据库] , 1359 阅读

/*物料需要数量需要仓库现存量仓库现存量仓库数量批次
A1 2 C1 C1         20         123
A1 2 C1 C2         30         111
A1 2 C1 C2         20         222
A1 2 C1 C3         10         211
A2 3 C4 C1         40         321
A2 3 C4 C4         50         222
A2 3 C4 C4         60         333
A2 3 C4 C5         70         223
我需要把上面的查询结果转换为下面的。
物料需要数量需要仓库 C1 C2 C3 C4 C5
A1 2 C1 20 50 10 0 0
A2 3 C4 40 0 0 110 70
*/
---------------------------------------------------------------建表
----------------判断表是否存在
declare num number;
begin
    select count(1) into num from user_tables where table_name='TEST';
    if num>0 then
      execute immediate 'drop table TEST';
    end if;
end;
----------------建表
CREATE TABLE TEST(
    WL VARCHAR2(10),
    XYSL INTEGER,
    XYCK VARCHAR2(10),
    XCLCK VARCHAR2(10),
    XCLCKSL INTEGER,
    PC INTEGER
);
----------------第一部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' ,        20,         123);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        30,         111);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' ,        20,         222);
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' ,        10,         211);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' ,        40,         321);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        50,         222);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' ,        60,         333);
INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' ,        70,         223);
COMMIT;
--select * from test;
---------------------------------------------------------------行转列的存储过程
CREATE OR REPLACE PROCEDURE P_TEST IS
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;

    BEGIN
      V_SQL := 'SELECT WL,XYSL,XYCK';

      FOR V_XCLCK IN CURSOR_1
      LOOP
        V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||
                 ''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;
      END LOOP;

      V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL;
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      EXECUTE IMMEDIATE V_SQL;
    END;
----------------------------------------------------------------结果
----------------执行存储过程，生成视图
BEGIN
P_TEST;
END;
----------------结果
SELECT * FROM RESULT T;
WL                                            XYSL XYCK               C1         C2         C3         C4         C5
---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
A1                                               2 C1                 20         50         10          0          0
A2                                               3 C4                 40          0          0        110         70

----------------第二部分测试数据
INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' ,        20,         124);
INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' ,        30,         121);
INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' ,        20,         322);
COMMIT;
----------------报告存储过程，生成视图
BEGIN
P_TEST;
END;
----------------结果
SELECT * FROM RESULT T;
WL     XYSL XYCK          C1       C2         C3         C4         C5         C6         C7         C8
----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------
A1        2 C1            20       50         10          0          0         20          0          0
A2        2 C1             0        0          0          0          0          0         30          0
A2        3 C4            40        0          0        110         70          0          0          0
A3        2 C1             0        0          0          0          0          0          0         20
--------------- 删除实体
DROP VIEW RESULT;
DROP PROCEDURE P_TEST;
DROP TABLE TEST;

[回复]

seo service london 说:
2024年2月21日 22:19

I am huge fan of guitar and want to learn and apply some tricks to play guitar easily.

[回复]

먹튀폴리스주소 说:
2024年2月28日 20:53

I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here.

[回复]

카지노탐구생활 说:
2024年2月28日 20:54

[回复]

토토사이트 说:
2024年2月28日 21:01

Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!

[回复]

안전토토사이트 说:
2024年2月28日 21:01

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.

[回复]

먹튀사이트조회 说:
2024年2月28日 21:01

This particular papers fabulous, and My spouse and i enjoy each of the perform that you have placed into this. I’m sure that you will be making a really useful place. I has been additionally pleased. Good perform!

[回复]

안전놀이터 说:
2024年2月28日 21:35

Thank you for sharing your info. I really appreciate your efforts and Thanks a lot for sharing a piece of wonderful information which I am looking for a longer period of time

[回复]

토토사이트조회 说:
2024年2月28日 21:35

Good – I should certainly pronounce, impressed with your web site. I had no trouble navigating through all tabs as well as related info ended up being truly easy to do to access. I recently found what I hoped for before you know it in the least. Quite unusual. Is likely to appreciate it for those who add forums or something, site theme

[回复]

먹튀검증 说:
2024年2月28日 21:35

[回复]

안전토토사이트 说:
2024年2月28日 21:52

Truly quite intriguing post. I was searching for this sort of data and delighted in perusing this one. Continue posting. Much obliged for sharing

[回复]

안전놀이터 说:
2024年2月28日 22:07

You are really a very high talented singer!!! I really love your music

[回复]

해외안전놀이터 说:
2024年2月28日 22:07

I trully appreciate your work and tips given by you is helpful to me.

[回复]

메이저사이트 说:
2024年2月28日 22:08

Choosing an escort is a life-changing experience, and it pays to make the right choice. So, make sure you scan through the available offers and make a selection that fits your lifestyle and budget. With the right amount of cash, you can have an unforgettable time with the perfect

[回复]

먹튀검증 说:
2024年2月28日 22:36

The post highlights the issue of absurd garbage declarations in Kartuzy, where unnecessary information, such as kinship between residents, is being collected. Such requirements seem unrelated to the purpose of garbage collection. It is essential to promote sensible policies and prioritize practicality. On a related note, using the NordVPN mod APK can enhance online privacy and security.

[回复]

메이저놀이터 说:
2024年2月28日 23:07

[回复]

먹튀대피소 说:
2024年2月28日 23:08

Very good topic, similar texts are I do not know if they are as good as your work out. These you will then see the most important thing, the application provides you a website a powerful important internet page

[回复]

안전놀이터 说:
2024年2月28日 23:08

I'm relating to this article this is called Perfection thank you so much for sharing. If you want to know what is Game Guardian app? Game Guardian is a cheat engine that allows you to change the values of variables in games. It is a popular tool among gamers who want to get an edge in games, it's free to get it now.

[回复]

스포츠토토사이트 说:
2024年2月28日 23:09

There's something about garbage declarations that just makes me laugh. It's like the absurdity of it all is hilarious, and I can't help but enjoy the absurdity of it all. Whether it's the guy who declares his car "tires good as new" or the person who insists their oven is "in perfect condition," there's something to enjoy in garbage declarations. They're just so ridiculous and absurd, and they make me laugh.

[回复]

메이저놀이터 说:
2024年2月28日 23:47

[回复]

먹튀사이트 说:
2024年2月29日 00:09

Greyspaces Furniture offers an exquisite range of sofa sets in Lahore, Pakistan, at competitive prices. With quality craftsmanship and stylish designs, your living space will be transformed. Discover comfort and elegance without breaking the bank at Greyspaces Furniture!

[回复]

토토먹튀검증 说:
2024年2月29日 00:19

[回复]

먹튀검증 说:
2024年2月29日 00:52

This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free. It is the old what goes around comes around routine.

[回复]

온라인카지노추천 说:
2024年2月29日 01:01

Thank you for sharing superb information. Your web site is so cool. I’m impressed by the details that you’ve on this website. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for more articles.

[回复]

카지노사이트목록 说:
2024年2月29日 01:02

[回复]

토토하이 说:
2024年2月29日 01:02

[回复]

เว็บไซท์ แทงบอลออนไล 说:
2024年2月29日 01:09

Oh my goodness! an excellent article dude. Thank you However I'm experiencing problem with ur rss. Do not know why Cannot register for it. Could there be any person getting identical rss difficulty? Anybody who knows kindly respond. Thnkx

[回复]

스포츠토토 说:
2024年2月29日 01:09

[回复]

토토먹튀검증 说:
2024年2月29日 01:10

[回复]

메이저놀이터순위 说:
2024年2月29日 01:42

Thumbs up guys your doing a really good job

[回复]

토토사이트 说:
2024年2月29日 02:13

You are really a very high talented singer!!! I really love your music

[回复]

토토사이트 说:
2024年2月29日 02:18

[回复]

메이저놀이터 说:
2024年2月29日 02:18

[回复]

먹튀신고 说:
2024年2月29日 02:18

[回复]

메이저사이트 说:
2024年2月29日 02:20

[回复]

우리카지노 说:
2024年2月29日 02:20

Thank you for sharing your info. I really appreciate your efforts and Thanks a lot for sharing a piece of wonderful information which I am looking for a longer period of time

[回复]

카지노사이트 说:
2024年2月29日 02:21

Hello. Great job. I did not anticipate this. This is a splendid articles

[回复]

온카지노 说:
2024年2月29日 02:21

Everyone needs a good pair or two of casual shoes for Mens and Walkeaz has you covered with our Best Mens Casual Shoes Collection. Add extraordinary style to your everyday excursions when you wear Best Mens Casual Shoes

小蒜头's Blog

Happy coding

oracle行转列（动态行转不定列)

小蒜头

分类

最新评论

最新留言

链接

功能