急！帮忙啊！oracle列转行问题

JJTwo 2009-06-02 11:45:21

NUM_SCRN_SIZE_ID NUM_CHNL_BACK_ID NUM_ICON_ID NUM_W NUM_H VC2_BACK_DIR VC2_ICON_DIR
1 1 20 24 240 320 1188787663-0aifusenkobe-4.jpg 1188787663-0aifusenkobe-4.jpg
2 1 20 25 240 320 1188787663-0aifusenkobe-4.jpg 1188787321-0aifusenkobe-3.jpg
3 1 20 26 240 320 1188787663-0aifusenkobe-4.jpg 0910-iverson-jiaodiantu-1.jpg
4 1 20 27 240 320 1188787663-0aifusenkobe-4.jpg 0910-chu-iverson-jiaodiantu.jpg

我想要的效果是

NUM_SCRN_SIZE_ID NUM_CHNL_BACK_ID NUM_ICON_ID NUM_W NUM_H VC2_BACK_DIR VC2_ICON_DIR1,VC2_ICON_DIR2,VC2_ICON_DIR3,VC2_ICON_DIR4

1 1 20 24 240 320 1188787663-0aifusenkobe-4.jpg 1188787663-0aifusenkobe-4.jpg 1188787321-0aifusenkobe-3.jpg
1188787321-0aifusenkobe-2.jpg 1188787321-0aifusenkobe-1.jpg

人都晕死了！

...全文

308 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

oraclelogan 2009-06-02

打赏
举报

不好意思，网速卡住了，发了3贴啊！抱歉啊！

oraclelogan 2009-06-02

打赏
举报

给你一个实例，你参考下：

create table t2(col1 varchar2(10),col2 varchar2(10)); 

insert into t2 values('001','vl1'); 

insert into t2 values('001','vl2'); 

insert into t2 values('001','vl3'); 

insert into t2 values('002','vl1'); 

insert into t2 values('002','vl2'); 



SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2 

FROM (SELECT COL1, 

               COL2, 

               MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN, 

               (ROW_NUMBER() OVER(ORDER BY COL1, COL2)) + 

               (DENSE_RANK() OVER(ORDER BY COL1)) NUMID 

          FROM T2) 

START WITH COL2 = COL2_MIN 

CONNECT BY NUMID - 1 = PRIOR NUMID 

GROUP BY COL1 



/*

COL1       COL2                                    

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

001        vl1,vl2,vl3                             

002        vl1,vl2                                 

2 rows selected

*/

oraclelogan 2009-06-02

打赏
举报

给你一个实例，你参考下：

create table t2(col1 varchar2(10),col2 varchar2(10)); 

insert into t2 values('001','vl1'); 

insert into t2 values('001','vl2'); 

insert into t2 values('001','vl3'); 

insert into t2 values('002','vl1'); 

insert into t2 values('002','vl2'); 



SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2 

FROM (SELECT COL1, 

               COL2, 

               MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN, 

               (ROW_NUMBER() OVER(ORDER BY COL1, COL2)) + 

               (DENSE_RANK() OVER(ORDER BY COL1)) NUMID 

          FROM T2) 

START WITH COL2 = COL2_MIN 

CONNECT BY NUMID - 1 = PRIOR NUMID 

GROUP BY COL1 



/*

COL1       COL2                                    

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

001        vl1,vl2,vl3                             

002        vl1,vl2                                 

2 rows selected

*/

oraclelogan 2009-06-02

打赏
举报

给你一个实例，你参考下：

create table t2(col1 varchar2(10),col2 varchar2(10)); 

insert into t2 values('001','vl1'); 

insert into t2 values('001','vl2'); 

insert into t2 values('001','vl3'); 

insert into t2 values('002','vl1'); 

insert into t2 values('002','vl2'); 



SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2 

FROM (SELECT COL1, 

               COL2, 

               MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN, 

               (ROW_NUMBER() OVER(ORDER BY COL1, COL2)) + 

               (DENSE_RANK() OVER(ORDER BY COL1)) NUMID 

          FROM T2) 

START WITH COL2 = COL2_MIN 

CONNECT BY NUMID - 1 = PRIOR NUMID 

GROUP BY COL1 



/*

COL1       COL2                                    

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

001        vl1,vl2,vl3                             

002        vl1,vl2                                 

2 rows selected

*/

oraclelogan 2009-06-02

打赏
举报

使用SYS_CONNECT_BY_PATH进行行列转换

JJTwo 2009-06-02

打赏
举报

我想把最后一列一个个取出来拼到第一行后面！

JJTwo 2009-06-02

打赏
举报

格式有点不对！

ojuju10 2009-06-02

打赏
举报

如果是固定的就直接用case when

不固定的就用动态的sql语句

robin_ares 2009-06-02

打赏
举报

不定行的行转列，还是在后台重新组织或写存储过程好

qin_phoenix 2009-06-02

打赏
举报

CREATE OR REPLACE TYPE STRCAT_TYPE AS OBJECT

(

  CURRENTSTR      VARCHAR2(4000),

  CURRENTSEPRATOR VARCHAR2(8),

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRCAT_TYPE)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT STRCAT_TYPE,

                                       VALUE IN VARCHAR2) RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN STRCAT_TYPE,

                                         RETURNVALUE OUT VARCHAR2,

                                         FLAGS       IN NUMBER)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRCAT_TYPE,

                                     CTX2 IN STRCAT_TYPE) RETURN NUMBER

);





CREATE OR REPLACE TYPE BODY STRCAT_TYPE IS

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRCAT_TYPE)

    RETURN NUMBER IS

  BEGIN

    SCTX := STRCAT_TYPE('', ',');

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT STRCAT_TYPE,

                                       VALUE IN VARCHAR2) RETURN NUMBER IS

  BEGIN

    IF SELF.CURRENTSTR IS NULL THEN

      SELF.CURRENTSTR := VALUE;

    ELSE

      SELF.CURRENTSTR := SELF.CURRENTSTR || CURRENTSEPRATOR || VALUE;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN STRCAT_TYPE,

                                         RETURNVALUE OUT VARCHAR2,

                                         FLAGS       IN NUMBER) RETURN NUMBER IS

  BEGIN

    RETURNVALUE := SELF.CURRENTSTR;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRCAT_TYPE,

                                     CTX2 IN STRCAT_TYPE) RETURN NUMBER IS

  BEGIN

    IF CTX2.CURRENTSTR IS NULL THEN

      SELF.CURRENTSTR := SELF.CURRENTSTR;

    ELSIF SELF.CURRENTSTR IS NULL THEN

      SELF.CURRENTSTR := CTX2.CURRENTSTR;

    ELSE

      SELF.CURRENTSTR := SELF.CURRENTSTR || CURRENTSEPRATOR ||

                         CTX2.CURRENTSTR;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

END;



CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;





select NUM_SCRN_SIZE_ID NUM_CHNL_BACK_ID NUM_ICON_ID NUM_W NUM_H VC2_BACK_DIR ,strcat (VC2_ICON_DIR )  from table where  group by NUM_SCRN_SIZE_ID ..