**
mysql数据库,对于多行转一行的写法
**
create table hz_temp(
id int primary key not null,
name varchar(32),
age int,
hobby varchar(32)
)
insert into hz_temp(id,name,age,hobby) values(1,‘张三’,15,‘打篮球’)
insert into hz_temp(id,name,age,hobby) values(2,‘张三’,15,‘打羽毛球’)
insert into hz_temp(id,name,age,hobby) values(3,‘李四’,14,‘打乒乓球’)
insert into hz_temp(id,name,age,hobby) values(4,‘王五’,16,‘写作’)
insert into hz_temp(id,name,age,hobby) values(5,‘王五’,16,‘唱歌’)
SQL语句:
select name,age,group_concat(hobby separator ‘,’) hobby from hz_temp group by name,age
运行结果:
如果想改变编码字符格式
可以这样写:
select name,age,CONVERT(group_concat(hobby separator ‘,’) USING utf8) hobby from hz_temp group by name,age
**
DB2数据库,对于多行转一行的写法
**
create table hz_temp(
id int primary key not null,
name varchar(32),
age int,
hobby varchar(32)
)
insert into hz_temp(id,name,age,hobby) values(1,‘张三’,15,‘打篮球’)
insert into hz_temp(id,name,age,hobby) values(2,‘张三’,15,‘打羽毛球’)
insert into hz_temp(id,name,age,hobby) values(3,‘李四’,14,‘打乒乓球’)
insert into hz_temp(id,name,age,hobby) values(4,‘王五’,16,‘写作’)
insert into hz_temp(id,name,age,hobby) values(5,‘王五’,16,‘唱歌’)
SQL语句:
select name,age,listagg(hobby,‘,’) hobby from hz_temp group by name,age
运行结果: