SQL Server converts multiple rows in one column into strings using the for XML Path.

SQL Code

2. Create TableTB ([ID]Int,[Name]Varchar(2 ))
4. Insert IntoTB
6. Select1,'A' Union All
8. Select2,'Bb' Union All
10. Select1,'Cc' Union All
12. Select3,'Dd' Union All
14. Select2,'Ee'
18. Select*FromTB
20. -- Query
22. Select
24. ID,
26. Name= Stuff ((Select ','+Name FromTBWhereId = T. IDForXML Path (''), 1, 1,'')
28. From
30. TB t
32. Group By
34. ID
42. Method 2:
46. Create FunctionGet_string (@ IDInt)
48. Returns Varchar(50)
50. As
52. Begin
54. Declare@Name Varchar(500)
56. Select@Name=Isnull(@Name+',','') +Name FromTBWhereId = @ ID
58. Return@Name
60. End
66. SelectID, DBO. get_string (ID)Name FromTBGroup ByID
70. -----------------------------------------------------------------------
72. In Oracle, there is a corresponding function, the sys_connect_by_path function.
76. Let's write down the Oracle function implementation here first.
80. SelectID
82. , Itrim (Max(Sys_connect_by_path (Name,',')))As Name
84. From
86. (SelectID
88. ,Name
90. , Lead (rnfirst) over (partitionBy No Order ByRnfirst) rnnext
92. From
94. (SelectA. ID
96. ,.Name
98. , Row_number () over (Order ByA. ID,.Name Desc) Rnfirst
100. From@ T a) tmptable1
102. ) Tmptable2
104. StartWithRnnextIs Null
106. Connect ByRnnext =PriorRnfirst
108. Group ByID
112. The preceding method is used to convert rows to columns in Oracle.
116. **************************************** * *** Let's take a look at sqlserver, because sqlserver does not provide similar functions, I do not know whether sqlserver2012 provides
120. However, SQL Server is easy to support.CrossApply andForThe XML Path combination is used, and both of them know what is going on and how to use it. But I really don't know whether there are two combinations to complete this function. It's incredible, I would like to thank the people on the Internet who wrote everything on their own to record the learned knowledge to their local computer and never put it on the Internet. Maybe it's the latest. I don't know a lot of work, all through the help of the network, we can put everything we know on the Internet and learn together.
124. Sqlserver:
128. SelectID,Substring(Names, 1, Len (names)-1)From@ TAsACrossApply
130. (Select Name+',' From@ TAsBWhereA. ID = B. IDForXML Path ('') D (names)
134. Group ByID, names