select company_cd,customer_seq_no,shop_cd,
[color=red] MAX(decode(item_cd,'29', item_details_name, NULL)) ITMN_29,
MAX(decode(item_cd,'30', item_details_name, NULL)) ITMN_30,
MAX(decode(item_cd,'31', item_details_name, NULL)) ITMN_31[/color]
from(
[color=red] SELECT item_cd,company_cd,shop_cd,customer_seq_no,LTRIM(MAX(SYS_CONNECT_BY_PATH(item_details_name,' ')),' ') item_details_name
FROM
(
SELECT item_cd,company_cd,shop_cd,customer_seq_no,item_details_name,MIN(item_details_name) OVER(PARTITION BY item_cd) item_details_name_MIN,
(ROW_NUMBER() OVER(ORDER BY item_cd,item_details_name))+(DENSE_RANK() OVER (ORDER BY item_cd)) NUMID
FROM
(
select mis.item_cd,tcffs.company_cd,tcffs.shop_cd,tcffs.customer_seq_no,mids.item_details_name
from t_customer_free_flag_shop tcffs,m_item_details_shop mids,m_item_shop mis
where tcffs.delete_flag='0' and mids.delete_flag='0' and mis.delete_flag='0'
and tcffs.company_cd=mis.company_cd and tcffs.shop_cd=mis.shop_cd
and tcffs.item_cd=mis.item_cd and tcffs.version_number=mis.version_number
and tcffs.item_details_cd=mids.item_details_cd
--and tcffs.item_cd and tcfis.item_details_cd in
--or and tcffs.item_cd and tcfis.item_details_cd in
--or and tcffs.item_cd and tcfis.item_details_cd in
)
)
START WITH item_details_name=item_details_name_MIN CONNECT BY NUMID-1=PRIOR NUMID
GROUP BY item_cd,company_cd,customer_seq_no,shop_cd[/color]
)
group by company_cd,customer_seq_no,shop_cd
第一个是行列转化,
第二个是多行转一列
[color=red] MAX(decode(item_cd,'29', item_details_name, NULL)) ITMN_29,
MAX(decode(item_cd,'30', item_details_name, NULL)) ITMN_30,
MAX(decode(item_cd,'31', item_details_name, NULL)) ITMN_31[/color]
from(
[color=red] SELECT item_cd,company_cd,shop_cd,customer_seq_no,LTRIM(MAX(SYS_CONNECT_BY_PATH(item_details_name,' ')),' ') item_details_name
FROM
(
SELECT item_cd,company_cd,shop_cd,customer_seq_no,item_details_name,MIN(item_details_name) OVER(PARTITION BY item_cd) item_details_name_MIN,
(ROW_NUMBER() OVER(ORDER BY item_cd,item_details_name))+(DENSE_RANK() OVER (ORDER BY item_cd)) NUMID
FROM
(
select mis.item_cd,tcffs.company_cd,tcffs.shop_cd,tcffs.customer_seq_no,mids.item_details_name
from t_customer_free_flag_shop tcffs,m_item_details_shop mids,m_item_shop mis
where tcffs.delete_flag='0' and mids.delete_flag='0' and mis.delete_flag='0'
and tcffs.company_cd=mis.company_cd and tcffs.shop_cd=mis.shop_cd
and tcffs.item_cd=mis.item_cd and tcffs.version_number=mis.version_number
and tcffs.item_details_cd=mids.item_details_cd
--and tcffs.item_cd and tcfis.item_details_cd in
--or and tcffs.item_cd and tcfis.item_details_cd in
--or and tcffs.item_cd and tcfis.item_details_cd in
)
)
START WITH item_details_name=item_details_name_MIN CONNECT BY NUMID-1=PRIOR NUMID
GROUP BY item_cd,company_cd,customer_seq_no,shop_cd[/color]
)
group by company_cd,customer_seq_no,shop_cd
第一个是行列转化,
第二个是多行转一列