行列转换和多行合并一个字段

最新推荐文章于 2023-01-06 14:38:13 发布

ahwen_2008

最新推荐文章于 2023-01-06 14:38:13 发布

阅读量647

点赞数

分类专栏： oracle 文章标签：行列转换和多行合并一个字段

本文链接：https://blog.youkuaiyun.com/ahwen_2008/article/details/83284874

版权

oracle 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用SQL进行复杂的数据转换方法，包括行列转换及多行转一列的技术实现细节。通过对具体SQL语句的解析，展示了如何利用decode函数和聚合函数MAX实现特定字段的选择与汇总。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

第一个是行列转化，
第二个是多行转一列