SQL Server Union table field Merge query

It is often encountered in statistical reports where the child table records are merged into one field. For example, the provincial table of the provincial economic level of the top five of the city statistics.

As in the next two tables: dbo province and dbo. City (well, you might be able to spit out why use the Chinese table name, actually I was to facilitate the search for replacement)

For the time being, the first five external conditions of the economic level are not considered, and it is expected that all cities will be joined by commas.

The following are the specific practices:

--query based on a child tableSELECTP.name as 'Province',    STUFF((SELECT ','+Name fromdbo. CityWHEREParent=C.parent forXML PATH ("')),1,1,"') as 'City' fromdbo. City C Left JOINdbo. Province P onC.parent=P.codeGROUP  byc.parent, P.nameORDER  byc.parent--query based on parent tableSELECTP.name as 'Province', XC. City as 'City' fromdbo. Province P Left JOIN(        SELECT             STUFF((SELECT ','+Name fromdbo. CityWHEREParent=C.parent forXML PATH ("')),1,1,"') as ' City', the Parent fromdbo. City CGROUP  byc.parent) XC onP.code=XC. ParentORDER  byP.code

There are two ways of querying, based on which the sub-table ignores provincial records without subordinate cities, such as municipalities, while the parent table displays null values in the child table collection columns, which are used in both scenarios.

Syntax Explanation:

STUFF ('STRING',1,1,')

Based on MSDN help, you can see that this is a string substitution function that will take a string of parameter 1, starting with the parameter 2 position (the database index usually starts at 1 instead of 0), intercepts the length as parameter 3, and the intercepted part is replaced with the parameter 4.

SELECT STATEMENT for XML PATH (")

The SELECT STATEMENT here is a regular query statement, and the result is an XML collection, so you can include a two-dimensional data table.
But the purpose of this query is to stitch together one-dimensional data, so the query statement here usually queries only one field and uses a delimiter before the field. The delimiter used in the example is a Chinese comma.
The specific for XML path syntax can be referenced in MSDN.

Reprint please indicate from the flying dust of the blog park.

SQL Server Union table field Merge query