sql查询id逗号拼接_sql查询列进行STUFF()拼接单引号逗号_xmlpathexcel

有时候查询出的某一列结果要作为其他数据库查询条件&＃xff0c;必须要做拼接转换

方法&＃xff1a;

SELECT [id] &＃61; stuff((SELECT distinct &＃39;,&＃39;&＃39;&＃39; &＃43; t.id &＃43; &＃39;&＃39;&＃39;&＃39; FROM table t WHERE t.id &＃61; 1 FOR xml path (&＃39;&＃39;)),1,1,&＃39;&＃39;);

方法使用如下&＃xff1a;

SELECT [Id] &＃61; STUFF((SELECT DISTINCT &＃39;,&＃39;&＃39;&＃39; &＃43; Ld &＃43; &＃39;&＃39;&＃39;&＃39; FROM BaseMeterFieldType AS b WHERE b.MeterId IN (SELECT Id FROM BaseTree WHERE CompanyId &＃61; &＃39;071B4795-E124-E711-80EA-C979862AE7CB&＃39;) FOR xml PATH(&＃39;&＃39;)),1,1,&＃39;&＃39;);

执行结果&＃xff1a;

以上报错是由于id为GUID&＃xff0c;且id需要小写&＃xff0c;所以需要进行类型转换&＃xff0c;方法如下&＃xff1a;

SELECT [Id] &＃61; STUFF((SELECT DISTINCT &＃39;,&＃39;&＃39;&＃39; &＃43; CAST(LOWER(b.Id) AS VARCHAR(4000)) &＃43; &＃39;&＃39;&＃39;&＃39; FROM BaseMeterFieldType AS b WHERE b.MeterId IN (SELECT Id FROM BaseTree WHERE CompanyId &＃61; &＃39;071B4795-E124-E711-80EA-C979862AE7CB&＃39;) FOR xml PATH(&＃39;&＃39;)),1,1,&＃39;&＃39;);

执行结果&＃xff1a;