스칼라 함수 >> 여러 row 한 필드로 가져오기

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GET_KIND_CODE_PROC](@b_id int)
 RETURNS VARCHAR(1000)
AS

Begin
 Declare @tot int, @idx int, @rtnValue varchar(2000)
 --@b_id int,
 --SET @b_id = 51320
 SET @idx = 1
 SET @rtnValue = N''

 Declare @tbTemp Table
 (
    idx int primary key identity
  , jk_code nvarchar(100)
 )

 INSERT INTO @tbTemp
 Select BK.jk_code From dbo.ALBA_BOARD_KIND_TB BK WITH(NOLOCK)
 INNER JOIN dbgen.alba.dbo.jobkind_tb JM  ON BK.jk_code = JM.jk_code
 Where BK.b_id = @b_id

 Select @tot = count(*) From @tbTemp

 While(@idx <= @tot)
  Begin
   SET @rtnValue = @rtnValue +(select isnull( jk_code,'')+N',' From @tbTemp Where idx = @idx)
   
   SET @idx = @idx +1
  End

 

 Return @rtnValue
End