sqlserverlistagg

❶ sql中，如何用同一ID拼接不同字符串

create table testTable(
id number,
name varchar2(200)
);

select id ,listagg( name, ',' ) within group ( order by id ) as name
from testTable
GROUP BY id;

❷ sql 查询排除一个字段的其他字段

1、创建测试表，此处以创建6个字段为例，实际100个字段的话，操作类似，

create table test_sel_cols(id number, password varchar2(200), remark1 varchar2(100), remark2 varchar2(100), remark3 varchar2(100), remark4 varchar2(100));

❸ SQL如何查询两个表连接明细表结果用分隔符分开

'''

CREATE TABLE T_Students

(

ID varchar(50),

F_Name varchar(50)

)

CREATE TABLE T_Subjects

(

Student_ID varchar(50),

Subject_ID varchar(50),

F_SubjectName varchar(50)

)

insert T_Students values ('01','A')

insert T_Subjects values ('01','1','语文')

insert T_Subjects values ('01','2','数学')

insert T_Students values ('02','B')

insert T_Subjects values ('02','1','语文')

insert T_Subjects values ('02','2','数学')

select a.ID,a.F_Name,stuff((select ','+Subject_ID from T_Subjects

where Student_ID=a.ID for xml path('')),1,1,'') as SubjectID,stuff((select ','+F_SubjectName from T_Subjects

where Student_ID=a.ID for xml path('')),1,1,'') as SubjectName

from T_STudents as a group by ID,F_Name

'''

请点击输入图片描

❹ sql 里 怎么把一列的值合并成一个字符串

1、创建测试表，

create table test_str_concat(id varchar2(20)， value varchar2(20));

❺ 数据库中，如何用SQL语句实现将某字段下的所有记录合成一条记录，用逗号隔开

MS Sqlserver（for xml path的巧用）:

select'T1'M,stuff((select','+CAST(T1asvarchar)fromTable_1forxmlpath('')),1,1,'')N
union
select'T2'M,stuff((select','+CAST(T2asvarchar)fromTable_2forxmlpath('')),1,1,'')N

Oracle（自带这样的函数）:

selectwm_concat(T1)NfromTable_1;--10g写法
selectlistagg(T1,',')withingroup(orderbyT1)NfromTable_1;--11g写法