SQL Server for XML Path statement application-column-to-row

I often see that the for XML Path is used by the experts in the forum. Because it is a search, the detailed usage is recorded.
In SQL Server, the for XML Path statement can be used to generate XML data for the queried data. Below are some examples of its application.

Declare @ temptable table (userid int, username nvarchar (50 ));
Insert into @ temptable (userid, username) values (1, 'A ')
Insert into @ temptable (userid, username) values (2, 'B ')
 
Select userid, username from @ temptable for XML Path Run this script and the following results are generated: <row>
<Userid> 1 </userid>
<Username> A </username>
</Row>
<Row>
<Userid> 2 </userid>
<Username> B </username>
</Row> you can see that the two rows of Data generate two nodes and modify the path parameter: Select userid, username from @ temptable for XML Path ('lzy ') run the preceding script again to generate the following results:

<Lzy>
<Userid> 1 </userid>
<Username> A </username>
</Lzy>
<Lzy>
<Userid> 2 </userid>
<Username> B </username>
</Lzy> you can see that the parameter in path () is the name of the control node, so you can see that if it is a null string (not without a parameter) what is the result? Select userid, username from @ temptable for XML Path ('') execute the above script to generate the result:

<Userid> 1 </userid>
<Username> A </username>
<Userid> 2 </userid>
<Username> B </username> so that the upper-level node is not displayed,We all know that in the path mode, the column name or column alias is processed as an XPATH expression. That is to say, it is the name of the column. How can we avoid specifying the column name and alias?

Select cast (userid as varchar) + '', username +'' from @ temptable for XML Path ('')

Run the above sentence to generate the result

1a2b

All data generates a row without any connection characters. Such data may be of no use and can be changed as follows:
Select cast (userid as varchar) + ',', username + '', ';' from @ temptable for XML Path ('')
Generate results
1, A; 2, B;
Now you understand, you can use the control parameters to generate the desired results, for example:
Select '{' + Cast (userid as varchar) + ',', '"' + username + '"', '}' from @ temptable for XML Path ('')
Generate results
{1, "a"} {2, "B "}
You can also generate other formats, which can be combined according to your needs.
The following is an application of data statistics. I hope you can think of more applications through the following examples.
Declare @ T1 table (userid int, username nvarchar (50), cityname nvarchar (50 ));
Insert into @ T1 (userid, username, cityname) values (1, 'A', 'shanghai ')
Insert into @ T1 (userid, username, cityname) values (2, 'B', 'beijing ')
Insert into @ T1 (userid, username, cityname) values (3, 'C', 'shanghai ')
Insert into @ T1 (userid, username, cityname) values (4, 'D', 'beijing ')
Insert into @ T1 (userid, username, cityname) values (5, 'E', 'shanghai ')
 
Select B. cityname, left (userlist, Len (userlist)-1) from (
Select cityname,
(Select username + ', 'From @ T1 where cityname = A. cityname for XML Path ('') as userlist
From @ T1
Group by cityname
) B

Result (User Name of each city)

Beijing B, d
Shanghai A, C, E