就是表数据拼装的问题,之前也发过帖子,有大侠说是行转列的问题,但感觉并不能解决,所以再发个帖子问一下
我只挑了几个数据,实际上应该要用个存储过程动态去拼装,然后放到一个新表里,现在问题就是我不知道怎么去实现,还请各位大侠指点,谢谢!
--这个表的情况如下,只挑选了三个PASSENGER_SEGMENT_ID的数据
SQL> select t.tax_name,t.fare,t.passenger_segment_id from et_paxseg_tax t
2 where t.passenger_segment_id in ('758169','758170','758144');TAX_NAME FARE PASSENGER_SEGMENT_ID
-------------------- ----------- ---------------------------------------
YQ 150.00 758144
XA 356.00 758144
CN 130.00 758144
YR 23.00 758144
YQ 32.00 758169
YR 230.00 758169
CN 90.00 758169
YQ 32.00 7581708 rows selected--下面是我想将这些数据拼装成的形式,就是把TAX_NAME的值和对应的FARE值拼装起来放在一个字段中,
--使PASSENGER_SEGMENT_ID唯一PASSENGER_SEGMENT_ID NEWCOLUMN
----------------------- -------------------------------------------------
758144 YQ:150.00;XA:356.00;CN:130.00;YR:23.00
758169 YQ:32.00;YR:230.00;CN:90.00
758170 YQ:32.00
我只挑了几个数据,实际上应该要用个存储过程动态去拼装,然后放到一个新表里,现在问题就是我不知道怎么去实现,还请各位大侠指点,谢谢!
--这个表的情况如下,只挑选了三个PASSENGER_SEGMENT_ID的数据
SQL> select t.tax_name,t.fare,t.passenger_segment_id from et_paxseg_tax t
2 where t.passenger_segment_id in ('758169','758170','758144');TAX_NAME FARE PASSENGER_SEGMENT_ID
-------------------- ----------- ---------------------------------------
YQ 150.00 758144
XA 356.00 758144
CN 130.00 758144
YR 23.00 758144
YQ 32.00 758169
YR 230.00 758169
CN 90.00 758169
YQ 32.00 7581708 rows selected--下面是我想将这些数据拼装成的形式,就是把TAX_NAME的值和对应的FARE值拼装起来放在一个字段中,
--使PASSENGER_SEGMENT_ID唯一PASSENGER_SEGMENT_ID NEWCOLUMN
----------------------- -------------------------------------------------
758144 YQ:150.00;XA:356.00;CN:130.00;YR:23.00
758169 YQ:32.00;YR:230.00;CN:90.00
758170 YQ:32.00
select PASSENGER_SEGMENT_ID,
replace(wm_concat(TAX_NAME||':'||FARE),',',';') as NEWCOLUMN
from et_paxseg_tax;
--漏了一个 group by
select PASSENGER_SEGMENT_ID,
replace(wm_concat(TAX_NAME||':'||FARE),',',';') as NEWCOLUMN
from et_paxseg_tax
group by PASSENGER_SEGMENT_ID;
wm_concat()是10g版本才有的,我们系统用的9i的,有其他方法吗?
with t as (
select 'YQ' tax_name, 150.00 fare, '758144' passenger_segment_id from dual union all
select 'XA' tax_name, 356.00 fare, '758144' passenger_segment_id from dual union all
select 'CN' tax_name, 130.00 fare, '758144' passenger_segment_id from dual union all
select 'YR' tax_name, 23.00 fare, '758144' passenger_segment_id from dual union all
select 'YQ' tax_name, 32.00 fare, '758169' passenger_segment_id from dual union all
select 'YR' tax_name, 230.00 fare, '758169' passenger_segment_id from dual union all
select 'CN' tax_name, 90.00 fare, '758169' passenger_segment_id from dual union all
select 'YQ' tax_name, 32.00 fare, '758170' passenger_segment_id from dual)
SELECT passenger_segment_id,
MAX(ltrim(sys_connect_by_path(tax_name || ':' || fare, ','), ','))
FROM (SELECT row_number() over(PARTITION BY passenger_segment_id ORDER BY tax_name) rn,
t.*
FROM t)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1
AND PRIOR passenger_segment_id = passenger_segment_id
GROUP BY passenger_segment_id;
connect by从9i就有了
--自己写一个wm_concat
--详见 http://topic.csdn.net/u/20100201/12/e926f235-0efa-4469-9188-ebe85d3afd92.html
---9楼我的回答