SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;
select * from t_col_row;
select j.id ,
max(decode(j.cn,'c1',j.cv)) as a ,
max(decode(j.cn,'c2',j.cv)) as b
from
(SELECT id, 'c1' cn, unitname cv
FROM test
UNION ALL
SELECT id, 'c2' cn, wentiname cv
FROM test) j
group by j.id
select * from test for update ;
select m.case_num,
max(decode(m.unit_code,'2',m.unit_name)) as a ,
max(decode(m.unit_code,'3',m.unit_name)) as b ,
max(decode(m.unit_code,'4',m.unit_name)) as c
from jjkg_ss_sjmj m
group by m.case_num
SELECT id, 'c1' cn, unitname cv
FROM test
UNION ALL
SELECT id, 'c2' cn, wentiname cv
FROM test;
select * from (select m.case_num ,m.police_num from jjkg_ss_sjmj m )
select id ,unitname||':'||wentiname as b from test
Oracle列转行拼接及多行拼接2010-09-28 12:00表结构和数据如下(表名Test):
NO VALUE NAME
1 a 测试1
1 b 测试2
1 c 测试3
1 d 测试4
2 e 测试5
4 f 测试6
4 g 测试7
Sql语句:
select No,
ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;
检索结果如下:
NO VALUE NAME
1 a;b;c;d 测试1;测试2;测试3;测试4
2 e 测试5
4 f;g 测试6;测试7
简单解释一下那个Sql吧:
1、最内层的Sql(即表tmpTable1),按No和Value排序,并列出行号:
select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a
该语句结果如下:
NO VALUE NAME RNFIRST
1 d 测试4 1
1 c 测试3 2
1 b 测试2 3
1 a 测试1 4
2 e 测试5 5
4 g 测试7 6
4 f 测试6 7
2、外层的Sql(即表tmpTable2),根据No分区,取出当前行对应的下一条记录的行号字段:
select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (这里是tmpTable1的SQL) tmpTable1
lead(rnFirst):取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序,并按No分区,分区就是如果下一行的No字段与当前行的No字段不相等时,不取下一行记录显示
该语句结果如下:
NO VALUE NAME RNFIRST RNNEXT
1 d 测试4 1 2
1 c 测试3 2 3
1 b 测试2 3 4
1 a 测试1 4 NULL
2 e 测试5 5 NULL
4 g 测试7 6 7
4 f 测试6 7 NULL
3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst
这个大概意思就是从rnNext为null的那条记录开始,递归查找,
如果前一记录的rnFirst字段等于当前记录的rnNext字段,就把2条记录的Value用分号连接起来,
大家可以先试试下面这个没有Max和Group的Sql:
select No,
sys_connect_by_path(Value, ';') as Value,
sys_connect_by_path(Name, ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
结果是:
NO VALUE NAME
1 ;a ;测试1
1 ;a;b ;测试1;测试2
1 ;a;b;c ;测试1;测试2;测试3
1 ;a;b;c;d ;测试1;测试2;测试3;测试4
2 ;e ;测试5
4 ;f ;测试6
4 ;f;g ;测试6;测试7
可以看到,每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max,再加个Group by No,得到的结果就是行转列的结果了
最后再加一个Ltrim,去掉最前面的那个分号,完成。
[color=red][b]奶奶的,最终的答案是:[/b][/color]
select id,
ltrim(max(sys_connect_by_path(s, ';')), ';') as unitname
from (select id,
s,
rnFirst,
lead(rnFirst) over(partition by id order by rnFirst) rnNext
from (select a.id,
a.s,
row_number() over(order by a.id, a.s desc) rnFirst
from (select v.id,v.unitname||':'||v.wentiname as s from test v) a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by id;
分享到:
相关推荐
sql列转行以及行转列的通用存储过程!mssqlserver版本!
sql 行转列 与列转行,oracle ,msssql等,详细数据库操作方法,各种例子,欢迎大家学习。、~
Sql语句实现表的行列转换,行转列,列转行
DB2 SQL 通过函数(CONCAT/POSSTR/LOCATE)实现行转列,列转行 可以按照标点把多列转换为一行,多行转换为一列
oracle的列转行问题 oracle的列转行问题
oracle developer 列转行 in的入参转换
SQL行专列列转行的存储过程 很实用的 SQL行专列列转行的存储过程 很实用的
mysql行转列(将同一列下的不同内容的几行数据,转换成几列显示)、列转行、行列汇总、合并显示
SQL Server 列转行代码,供大伙参考
sql2005列转行存储过程代码,此代码分静态列表列转行和动态列表列转行,本代码在sql2005上测试通过。
精典的SQL语句。行转列,列转行的语句精典的SQL语句。行转列,列转行的语句
SqlServer如何进行行转列和列转行方法
SqlServer列转行的另一种方式!
可以把一列转成一行…… 分隔符默认为英文逗号,也可以转成INSERT 中VALUES()后面的单引号加逗号的形式。 以前用T-SQL时,总是把一列复制到EXCEL中,转置,然后复制到记本事中,把一空格替换成逗号……,这个工具...
sqlserver使用UNPIVOT函数列转行
主要介绍了Python pandas 列转行操作详解(类似hive中explode方法),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧
SQL列转行的方法 介绍的是两种列转行的方法。简单易懂...
Mysql 行转列,列转行 SQL语句和示例表结构SQL Mysql 行转列,列转行 SQL语句和示例表结构SQL
EXCEL横转竖(列转行)宏工具。可以用以小区排列转换等等。
oracle列转行的方法,有些时候页面显示要通过后台的列转行去实现,我们可以直接从sql中解决后台很复杂的问题