Stuff 和 用于 Xml 路径"怎么在 SQL Server 中工作?

本教程将介绍Stuff 和“用于 Xml 路径"如何在 SQL Server 中工作?的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

Stuff 和 用于 Xml 路径"怎么在 SQL Server 中工作? 教程 第1张

问题描述

Table is:

Id Name
1 aaa
1 bbb
1 ccc
1 ddd
1 eee

Required output:

Id abc
1 aaa,bbb,ccc,ddd,eee

Query:

SELECT ID, 
 abc = STUFF(
  (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
) 
FROM temp1 GROUP BY id

This query is working properly. But I just need the explanation how it works or is there any other or short way to do this.

I am getting very confused to understand this.

解决方案

Here is how it works:

1. Get XML element string with FOR XML

Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:

SELECT ',' + name 
  FROM temp1
  FOR XML PATH ('')

By passing in a blank string (FOR XML PATH('')), we get the following instead:

,aaa,bbb,ccc,ddd,eee

2. Remove leading comma with STUFF

The STUFF statement literally "stuffs" one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.

SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1

The parameters of STUFF are:

    The string to be "stuffed" (in our case the full list of name with a
    leading comma)

    The location to start deleting and inserting characters (1, we’re stuffing into a blank string)

    The number of characters to delete (1, being the leading comma)

So we end up with:

aaa,bbb,ccc,ddd,eee

3. Join on id to get full list

Next we just join this on the list of id in the temp table, to get a list of IDs with name:

SELECT ID,  abc = STUFF(
 (SELECT ',' + name 
  FROM temp1 t1
  WHERE t1.id = t2.id
  FOR XML PATH (''))
 , 1, 1, '') from temp1 t2
group by id;

And we have our result:

Id Name
1 aaa,bbb,ccc,ddd,eee

好了关于Stuff 和“用于 Xml 路径"怎么在 SQL Server 中工作?的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。