oracle行列转换实例: create table t22(Stu_id varchar2(20),Stu_name varchar2(10),Class_id varchar2(10)); insert into t22 values('101','狗剩1','1'); insert into t22 values('102','狗剩2','1'); insert into t22 values('103','狗剩3','1'); insert into t22 values('201','2柱子1','2'); insert into t22 values('202','2柱子2 ','2'); insert into t22 values('203','2柱子3 ','2'); commit; ------------------oracle 9i ------------------------------------------------- SELECT Class_id, LTRIM(MAX(SYS_CONNECT_BY_PATH(Stu_name, ',')), ',') Stu_name FROM (SELECT Class_id, Stu_name, MIN(Stu_name) OVER(PARTITION BY Class_id) Stu_name_MIN, (ROW_NUMBER() OVER(ORDER BY Class_id, Stu_name)) + (DENSE_RANK() OVER(ORDER BY Class_id)) NUMID FROM T22) START WITH Stu_name = Stu_name_MIN CONNECT BY NUMID - 1 = PRIOR NUMID GROUP BY Class_id;------------------oracle 10G --------------------------------- select Class_id, wmsys.wm_concat(Stu_name) from t22 group by Class_id ------------------执行结果如下--------------------------------- 1 1 狗剩1,狗剩2,狗剩3 2 2 2柱子1,2柱子2 ,2柱子3
不行啊.我想做的就是环比create table INCOME_COMPARISON ( ID NUMBER not null, SP_CODE VARCHAR2(50), GSM VARCHAR2(50), TOTAL VARCHAR2(50), TYPE NUMBER, CREATE_TIME TIMESTAMP(6), TABLE_DATE VARCHAR2(50), SP_NUMBER VARCHAR2(50) )这个是我的表结构 table_date=2月的total-table_date=1月的total/table_date=1的total 显示的时候就是 1月的total 2月的total 环比 3月的total 环比 12 22 0% 32 0% 34 33 34 44 123 44大概是这样的, 之前是我没表达明白不好意思啊.
SELECT Class_id, LTRIM(MAX(SYS_CONNECT_BY_PATH(Stu_name, ',')), ',') Stu_name FROM (SELECT Class_id, Stu_name, MIN(Stu_name) OVER(PARTITION BY Class_id) Stu_name_MIN, (ROW_NUMBER() OVER(ORDER BY Class_id, Stu_name)) + (DENSE_RANK() OVER(ORDER BY Class_id)) NUMID FROM T22) START WITH Stu_name = Stu_name_MIN CONNECT BY NUMID - 1 = PRIOR NUMID GROUP BY Class_id;
LZ好像不是这个意思哟。好像就是想列转行试试 when case或者decode你参考一下这个帖 http://topic.csdn.net/u/20090627/10/f1d34c6d-c2fa-4db0-8ace-9253b1484324.html
用decode来进行
create table t22(Stu_id varchar2(20),Stu_name varchar2(10),Class_id varchar2(10));
insert into t22 values('101','狗剩1','1');
insert into t22 values('102','狗剩2','1');
insert into t22 values('103','狗剩3','1');
insert into t22 values('201','2柱子1','2');
insert into t22 values('202','2柱子2 ','2');
insert into t22 values('203','2柱子3 ','2');
commit;
------------------oracle 9i -------------------------------------------------
SELECT Class_id, LTRIM(MAX(SYS_CONNECT_BY_PATH(Stu_name, ',')), ',') Stu_name
FROM (SELECT Class_id,
Stu_name,
MIN(Stu_name) OVER(PARTITION BY Class_id) Stu_name_MIN,
(ROW_NUMBER() OVER(ORDER BY Class_id, Stu_name)) +
(DENSE_RANK() OVER(ORDER BY Class_id)) NUMID
FROM T22)
START WITH Stu_name = Stu_name_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY Class_id;------------------oracle 10G ---------------------------------
select Class_id, wmsys.wm_concat(Stu_name)
from t22
group by Class_id
------------------执行结果如下---------------------------------
1 1 狗剩1,狗剩2,狗剩3
2 2 2柱子1,2柱子2 ,2柱子3
(
ID NUMBER not null,
SP_CODE VARCHAR2(50),
GSM VARCHAR2(50),
TOTAL VARCHAR2(50),
TYPE NUMBER,
CREATE_TIME TIMESTAMP(6),
TABLE_DATE VARCHAR2(50),
SP_NUMBER VARCHAR2(50)
)这个是我的表结构
table_date=2月的total-table_date=1月的total/table_date=1的total
显示的时候就是
1月的total 2月的total 环比 3月的total 环比
12 22 0% 32 0%
34 33
34 44
123 44大概是这样的,
之前是我没表达明白不好意思啊.
FROM (SELECT Class_id,
Stu_name,
MIN(Stu_name) OVER(PARTITION BY Class_id) Stu_name_MIN,
(ROW_NUMBER() OVER(ORDER BY Class_id, Stu_name)) +
(DENSE_RANK() OVER(ORDER BY Class_id)) NUMID
FROM T22)
START WITH Stu_name = Stu_name_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY Class_id;
LZ好像不是这个意思哟。好像就是想列转行试试
when case或者decode你参考一下这个帖
http://topic.csdn.net/u/20090627/10/f1d34c6d-c2fa-4db0-8ace-9253b1484324.html