Interesting SQL multi-row data Splicing

The SQL query to be implemented is very primitive:

You must query the data in the second table from the first table. After querying the Internet, you can write the following SQL statement:

Copy codeThe Code is as follows: select * from userino

SELECT * FROM (
Select distinct userpart
FROM userino
)
Outer apply (
SELECT
[Usernames] = replace (SELECT username as value FROM userino N
WHERE userpart = A. userpart order by n. username asc for xml auto), '"/> <N value = "','/')
, '<N value = "',''), '"/> ','')
) N
Outer apply (
SELECT
[Username_cns] = replace (SELECT username_cn as value FROM userino M
WHERE userpart = A. userpart order by m. username asc for xml auto), '"/> <M value = "','/')
, '<M value = "',''), '"/> ','')

) M

Analyze the SQL statement as follows:

The total points used are: outer apply, for xml auto. I have not studied SQL Server very deeply, so let's record it.

Outer apply is a query method supported by SQL2005. Similar to connection query, it concatenates two query results. However, when you use outer apply, you can use the previous query results in the query after Apply.

For example:Copy codeThe Code is as follows: select * from
(Select * from userino)
Cross join (select username from userino
Where username = A. username) B

Select * from
(Select * from userino)
Join (select username from userino) B on a. username = B. username

Select * from
(Select * from userino)
Outer apply (select username from userino
Where username = A. username) B

The first SQL statement is obviously incorrect for two reasons: 1. Cross Join is unconditional, and 2. SQL Server reports the following error:

The multi-part identifier "A. username" cocould not be bound.

It may be said that conditional Join queries are not written in this way. They should be written in the second SQL statement, in fact, the effect of using Outer apply in the third SQL statement is the same.

However, Outer Apply can achieve the following effects:Copy codeThe Code is as follows: select * from
(Select * from userino)
Outer apply (select [value] = a. username + 'test') B

This may be a little troublesome to directly use join. The above example may be meaningless. In fact, SQL2005 proposed the Apply join method mainly to use the results of the executed query statement in connection query.

In addition to "outer apply", SQL Server also has CROSS APPLY. The difference is mainly in the processing of Null values.

For xml auto is mainly used to directly return SQL query results into XML statements. For Xml, RAW and EXPLICIT are available in addition to auto. FOR details, see "super simple: Using FOR XML AUTO to control XML output".

In the SQL text introduced at the beginning of this article, we use the two features above. First, we use Outer Apply to implement the grouping effect similar to using userpart to filter the users in each userpart, then, because the filtered results are multiple rows, we use for xml to splice multiple rows of data into xml, and finally split the xml ....

In summary, I feel that this implementation method is unique and I have learned some of the features of SQL Server. I would like to share it with you.