Oracel 中实现任意行转列（将一列多行转为一个字符串连接显示）

需求：

课程表：tb_Course

CID	CN(Course Name)
1	C1
2	C2

 

区域国家表: tb_Area

AID	AN(Area Name)	CoID (Country ID)
1	A1	1
1	A1	…
1	A1	n
2	A2	n+1
2	A2	…
2	A2	2n
3	A3	2n+1
3	A3	…
3	A3	3n

课程到国家的同步状态 tb_record

CID(Course ID)	CoID(Country ID)	status
		0，1，2

 

展示样例：

CID	DistributeArea (include tb_record.status is 2)	No_DistributeArea (include tb_record.status is 0, 1)
1	A1(2);A2(2)	A3(1)
2	A3(2)	A1(0);A2(1)

                                    

实现：

第一步：先关联 tb_Area 和 tb_record 得到区域的同步状态AS以及区域分发状态AD：

Select tbR.CID, tbA.AN decode(min(tbR.status), ‘0’, ‘0’, ‘1’, ‘1’, ‘2’) AS, decode(min(tbR.status), ‘2’, ‘1’, ‘0’) AD

From tb_Area tbA left join tb_record tbR on tbA.CoID = tbR.CoID

Group by tbR.CID, tbA.AN 结果表：T1

CID	AN	AS	AD
1	A1	2	1
1	A2	2	1
1	A3	1	0
2	A1	0	0
2	A2	1	0
2	A3	2	1

 

第二步：思路是先构建一颗树，然后再将树转成列：

方法1：构建树，先设法构建父子关系表 T2

1、先给表排序获得行号作为父节点ID

Select T1.CID, T1.AN, T1.AS, T1.AD, rownum over(order by T1.CID,T1.AD) rnFirst

From T1 得到T2

 

CID	AN	AS	AD	rnFirst
1	A1	2	1	1
1	A2	2	1	2
1	A3	1	0	3
2	A1	0	0	4
2	A2	1	0	5
2	A3	2	1	6

 

2、使用lead函数和分区特性得到子节点id

Select T2.CID, T2.AN, T2.AS, T2.AD, T2.rnFirst, lead(T2.rnFirst) over(partition by T2.CID,T2.AD order by T2.rnFirst) rnNext From T2 得到T3

CID	AS	AD	rnFirst	rnNext
1	2	1	1	2
1	2	1	2	Null
1	1	0	3	Null
2	0	0	4	5
2	1	0	5	Null
2	2	1	6	Null

 

3、构造树，利用sys_connect_by_path函数链接字符串

Select T3.CID, T3.AD, sys_connect_by_path(T3.AN ||’(‘ || T3.AS || ‘)’,,) Area

From T3

Start with T3.rnNext is Null

Connect by rnNext = prior rnFirst 得到T4

CID	AD	Area
1	1	,A1(2)
1	1	,A1(2),A2(2)
1	0	,A3(1)
2	0	,A1(0)
2	0	,A1(0),A2(1)
2	1	,A3(2)

 

合成一个语句：

Select T3.CID, T3.AD, sys_connect_by_path(T3.AN ||’(‘ || T3.AS || ‘)’,,) Area

From (Select T2.CID, T2.AN, T2.AS, T2.AD, T2.rnFirst, lead(T2.rnFirst) over(partition by T2.CID,T2.AD order by T2.rnFirst) rnNext

From (Select T1.CID, T1.AN, T1.AS, T1.AD, rownum over(order by T1.CID,T1.AD) rnFirst

From T1

     ) T2

      ) T3

Start with T3.rnNext is Null

Connect by rnNext = prior rnFirst

方法二：10G 中的wmsys.wm_concat 方法  

Select T1.CID, T1.Ad, wmsys.wm_concat(T3.AN ||’(‘ || T3.AS || ‘)’) over(partition by T1.CID, T1.AD order by rownum) Area

From T1 

 

第三步、分组取最大值：

Select T4.CID, T4.AD, ltrim(max(t4.Arear),,) Area From T4 得到T5

CID	AD	Aarer
1	1	A1(2),A2(2)
1	0	A3(1)
2	0	A1(0),A2(1)
2	1	A3(2)

 

第四步：行转列

Select T5.CID, max(decode(T5.AD, ‘1’, T5.Area, ‘’)) v1, max(decode(T5.AD, ‘0’, T5.Area, ‘’)) v2

From T5

Group by T5.CID

CID	V1	V2
1	A1(2),A2(2)	A3(1)
2	A3(2)	A1(0),A2(1)