Use functions and cursors to convert Multiple SQL rows into one column.

Sometimes in some systems, we need to use SQL statements to display data in one-to-multiple Relational Tables in the form of a row of each record. This article uses functions and cursors to implement this function.
The example table is described as follows:
A user table and document table

Now you want to implement the following functions to query the synthesis of each user and the corresponding document name. The following results
Userid username docnames
1 fengyan SQL basics, Asp.net entry
2 eflylab

Note that the last column is similar to statistics. This makes it impossible to use left join, so that the first user will have two rows, not expected
In fact, a lot of time needs to be done using SQL, instead Program In! If?
You can write a function to complete this function.
Create   Function F_getuserdocsbyuserid ( @ Userid   Int )
Returns   Varchar ( 8000 )
As  
Begin  
Declare   @ Str   Varchar ( 8000 )
Set   @ Str = ''  
Select   @ Str = @ Str + ' , ' + Docname From Doc Where Userid = @ Userid  
If ( Len ( @ Str ) > 1 )
Set   @ Str = Right ( @ Str , Len ( @ Str ) - 1 )
Return ( @ Str )

The corresponding query statement isSelectU.*, Study. DBO. f_getuserdocsbyuserid (U. userid)AsDocsFrom [User] AsU

Running Effect

This example shows how to convert multiple rows into one column. In practice, the results for docs may be more complex.
You need some flexibility!

you can use a cursor in the second method. For many friends who do not understand or do not apply a cursor, you can learn it! -- define result set table variables
declare @ T table (userid varchar ( 10 ), username varchar ( 100 ), docnames varchar ( 8000 )

-- Define and merge the cursor
Declare Mycursor Cursor
For
Select U. * , D.doc name From   [ User ] U Left   Join   [ Doc ] D On U. userid = D. userid Order   By U. userid

Declare   @ Userid_old   Int , @ Userid   Int , @ Username   Varchar ( 50 ), @ Oldusername   Varchar ( 50 ), @ Docname   Varchar ( 50 ), @ S   Varchar ( 100 )
-- Open cursor

Open Mycursor
Fetch Mycursor Into   @ Userid , @ Username , @ Docname
Select   @ Userid_old = @ Userid , @ S = '' , @ Oldusername = @ Username
While   @ Fetch_status = 0 -- Cycle when cursor has data
Begin
If   @ Userid = @ Userid_old -- If the current record is the same as the previous user ID, change the value of the variable @ s.
Select   @ S = @ S + ' , ' + @ Docname
Else
Begin -- Otherwise, it is the next user. You should add the user.
-- Add operation when userid is changed
Insert   @ T   Values ( @ Userid_old , @ Oldusername , Stuff ( @ S , 1 , 1 , '' ))
-- Assign a new file name to @ s and update @ userid_old, @ oldusername
Select   @ S = ' , ' + @ Docname , @ Userid_old = @ Userid , @ Oldusername = @ Username
End
Fetch Mycursor Into   @ Userid , @ Username , @ Docname
End
Insert   @ T   Values ( @ Userid_old , @ Oldusername , Stuff ( @ S , 1 , 1 , '' ))
Close Mycursor
Deallocate Mycursor
-- Display result
Select   *   From   @ T

The running result is as follows:

As expected, a left join query is used here.

Select U. * , D.doc name From   [ User ] U Left   Join   [ Doc ] D On U. userid = D. userid Order   By U. userid

The execution result is as follows:

In method 2, the cursor is used to loop in this result table. Use a variable result Table @ T in the middle to save the result to @ T. The final @ T is the required result.