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寫法