通用多行轉一行（SQL2008+）

USE [SGPZ]
GO

CREATE TYPE RC AS TABLE(
R1 [varchar](MAX) ,
R2 [varchar](MAX)
)

ALTER FUNCTION fn_rc ( @rc rc READONLY )
RETURNS @re TABLE
    (
      R1 [varchar](MAX) ,
      R2 [varchar](MAX)
    )
AS
    BEGIN       

        INSERT  @re
                SELECT  R1, R2 = STUFF(( SELECT ',' + R2
                                         FROM   @rc t
                                         WHERE  R1 = NT.R1
                                       FOR
                                         XML PATH('')
                                       ), 1, 1, '')
                FROM    @rc nt
                GROUP BY R1
        RETURN
   
    END 

 
 
DECLARE @RC AS RC
INSERT  @RC
        SELECT  AreaOrganID, stuname
        FROM    student
SELECT  * from dbo.fn_rc(@RC)