mssql for xml path
Posted by Albert 3242Day 18Hour 3Min 49Sec ago [2015-07-03]
여러 컬럼 값을 하나의 값으로 콤마로 구분하여 하나의 필드값으로 넣어줄수 있다.
-- =================================================
-- Author: 이성
-- Create date: 2015-07-03
-- Description:
-- SELECT dbo.GetColumToraw('PKUHU0001','2015',1,'albert',80,1)
-- =================================================
CREATE FUNCTION [dbo].[GetColumToraw](
@process_cd nvarchar(20),
@process_year nvarchar(20),
@process_term int,
@user_id nvarchar(20),
@parent_cd nvarchar(20),
@type_cd int
)
RETURNS VARCHAR(230)
AS
BEGIN
Declare @columns varchar(max);
Set @columns = ( select
b.cd_nm + ', '
from
TCN_pku_enroll_info_sub as a
left outer join TCN_CODE as b on
A.LANGUAGE_CD = B.LANGUAGE_CD
AND b.USER_CD = a.IDX_CD
where
process_cd = @process_cd and process_year = @process_year and process_term =@process_term and user_id = @user_id
and b.parent_cd = @parent_cd and a.TYPE_CD = @type_cd for xml path(''))
return substring(@columns,0,len(@columns))
END
GO