concat row to one column mssql

 

select p_name, p_cname + p_cgname as p_cname,

p_hkid , p_hphone2, PersonalEmail,PositionName, PositionCode,

ConfigService.Code + '  ' + ConfigDepartment.Code as ServiceDepartmentCode, RecruitmentApplicant.RecruitmentId

from RecruitmentApplicant

left join RecruitmentPosition on RecruitmentPosition.RecruitmentId = RecruitmentApplicant.RecruitmentId and  (RecruitmentPosition.InactiveTime is null or RecruitmentPosition.InactiveTime >= getDate()) 

left join Position on Position.PositionId = RecruitmentPosition.PositionId and  (Position.InactiveTime is null or Position.InactiveTime >= getDate())  

left join ConfigDepartment on ConfigDepartment.DepartmentId = Position.DepartmentId  and  (ConfigDepartment.InactiveTime is null or ConfigDepartment.InactiveTime >= getDate())  

left join ConfigService on ConfigService.serviceId = Position.serviceId  and  (ConfigService.InactiveTime is null or ConfigService.InactiveTime >= getDate())

where ApplicantId = 8647

and  (RecruitmentApplicant.InactiveTime is null or RecruitmentApplicant.InactiveTime >= getDate()) 

Select 

(

SUBSTRING(

        (

            SELECT ','+ PositionName  AS [text()]

             from RecruitmentPosition

left join Position on Position.PositionId = RecruitmentPosition.PositionId and  (Position.InactiveTime is null or Position.InactiveTime >= getDate())  

where RecruitmentPosition.RecruitmentId = 12865 and  (RecruitmentPosition.InactiveTime is null or RecruitmentPosition.InactiveTime >= getDate()) 

            ORDER BY PositionName

            FOR XML PATH ('')

        ), 2, 1000)

) as PositionName

 Select 

(

SUBSTRING(

        (

            SELECT ','+ PositionCode  AS [text()]

             from RecruitmentPosition

left join Position on Position.PositionId = RecruitmentPosition.PositionId and  (Position.InactiveTime is null or Position.InactiveTime >= getDate())  

left join ConfigDepartment on ConfigDepartment.DepartmentId = Position.DepartmentId  and  (ConfigDepartment.InactiveTime is null or ConfigDepartment.InactiveTime >= getDate())  

left join ConfigService on ConfigService.serviceId = Position.serviceId  and  (ConfigService.InactiveTime is null or ConfigService.InactiveTime >= getDate())

where RecruitmentPosition.RecruitmentId = 12865 and  (RecruitmentPosition.InactiveTime is null or RecruitmentPosition.InactiveTime >= getDate()) 

            ORDER BY PositionName

            FOR XML PATH ('')

        ), 2, 1000)

) as PositionName

 

 

 

 

select Replace(ServiceDepartmentCode, ' ' , '') from (

Select 

(

SUBSTRING(

(

SELECT ','+( ConfigService.Code + '/' + ConfigDepartment.Code)  AS [text()]

from RecruitmentPosition

left join Position on Position.PositionId = RecruitmentPosition.PositionId and  (Position.InactiveTime is null or Position.InactiveTime >= getDate())  

left join ConfigDepartment on ConfigDepartment.DepartmentId = Position.DepartmentId  and  (ConfigDepartment.InactiveTime is null or ConfigDepartment.InactiveTime >= getDate())  

left join ConfigService on ConfigService.serviceId = Position.serviceId  and  (ConfigService.InactiveTime is null or ConfigService.InactiveTime >= getDate())

where RecruitmentPosition.RecruitmentId = 12865 and  (RecruitmentPosition.InactiveTime is null or RecruitmentPosition.InactiveTime >= getDate()) 

ORDER BY PositionName

FOR XML PATH ('')

), 2, 1000)

) as ServiceDepartmentCode

) t3

 

 

reference
https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv