通用多行转一行(SQL2008+)

时间:2021-09-03 10:29:03


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)