SQL Server Performance

String Concatenation

Discussion in 'T-SQL Performance Tuning for Developers' started by garudasu, Dec 19, 2008.

  1. garudasu New Member

    Hi I posted in another forums, but dint get any reply. So Posting it here hoping for a reply..
    I am USing the Below Query as a part of a stored Procedure To Get all the Skill Names of an Employee

    DECLARE @Skill_NM VARCHAR(MAX)
    SET @Skill_nm=''
    SELECT @Skill_NM=@Skill_NM +s.skill_nm + ', 'FROM temployee_skills es
    INNER JOIN tskill s
    ON es.skill_nbr=s.skill_nbr
    WHERE es.employee_nbr =81
    SELECT SUBSTRING(@Skill_NM,1,LEN(@skill_nm)-2)

    i have columns in Tskill table like this

    Skill_nbr Skill_nm
    1 .net
    2 sql
    3 vb
    and in temployee_skills i have data like this

    Employee_nbr skill_nbr
    1 1
    1 2
    2 3
    2 2
    3 1

    Bu using the above Query i can get the the skill Names of an employee..
    But now i need to display skill names of more than one employee
    .

    i'm using the below query for it

    declare @employee_nbr varchar(max)
    DECLARE @Skill_NM VARCHAR(MAX)
    set @Employee_nbr='81,69'
    SET @Skill_nm=''
    SELECT @Skill_NM=@Skill_NM +s.skill_nm + ', 'FROM temployee_skills es
    INNER JOIN tskill s
    ON es.skill_nbr=s.skill_nbr
    WHERE ',' + @Employee_nbr + ',' like '%,'+ cast(es.employee_nbr as varchar(20)) + ',%'
    AND es.Active_FG=0
    SELECT SUBSTRING(@Skill_NM,1,LEN(@skill_nm)-2)

    But this query displays all the skills of the employees in one single column concatenated. Is there any way to get the records individually for each employee..
    like
    employee number skill name
    1 .net , sql
    2 sql,vb

    Please suggest me a way to do this
    Thanks Before HAnd..

  2. FrankKalis Moderator

  3. FrankKalis Moderator

Share This Page