String Concatenation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String Concatenation

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-19 : 09:43:57

Hi ,
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..

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-19 : 09:51:45

Look for PIVOT in bol. Alternatively, there are lots of similar topics in this forum also.

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-19 : 13:07:45

I dont have much knowledge of pivot. I just know it as an alternative for Case- Group By .
Can u tell me how can i implement it coz i dint understand it much from other posts

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-19 : 22:37:25

Refer Books Online for pivot operator

Jai Krishna

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-20 : 00:23:53

This is what i've tried coz i really have poor knowledge on pivot.
Sorry folks if i'm troubling anyone.. But i'm not getting this...

declare @skill_nm varchar(max)
set @skill_nm=''
SELECT es.employee_nbr FROM temployee_skills es
inner join tskill s PIVOT
( @skill_nm=@skill_nm+''+s.skill_nm FOR employee_nbr IN(81,65)
) AS p

I Get an Error like this

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@skill_nm'.

Please Help me out in this

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-20 : 00:50:10

Try like this

Select distinct tt.employee_nbr,Left(so.list,Len(so.list)-1)as skill_nm
From(select to.employee_nbr,t.skill_nm from Tskill t inner join tempskill to
On t.skill_nbr = to. Skill_nbr)tt cross apply
(select skill_nm + ',' as [text()] from Tskill r inner join tempskill z
On r.skill_nbr=z.skill_nbr
Where z.employee_nbr=ty.employee_nbr
For XML path(' ') so( list)

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-20 : 12:55:49

When i use the below I'm getting an error like
Incorrect Sysntacx near 'so'
As i'm not aware of cross apply,I dint understand this Query much, so dint know where to modify to avoid the error..

Select distinct tt.employee_nbr,Left(so.list,Len(so.list)-1)as skill_nm
From(select to.employee_nbr,t.skill_nm from Tskill t inner join temployee_skill to1
On t.skill_nbr = to1. Skill_nbr)tt cross apply
(select skill_nm + ',' as [text()] from Tskill r inner join temployee_skill z
On r.skill_nbr=z.skill_nbr
Where z.employee_nbr=ty.employee_nbr
For XML path(' ') so( list)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-20 : 13:34:54

quote:
Originally posted by sodeep

Try like this

Select distinct tt.employee_nbr,Left(so.list,Len(so.list)-1)as skill_nm
From(select to.employee_nbr,t.skill_nm from Tskill t inner join tempskill to
On t.skill_nbr = to. Skill_nbr)tt cross apply
(select skill_nm + ',' as [text()] from Tskill r inner join tempskill z
On r.skill_nbr=z.skill_nbr
Where z.employee_nbr=tt.employer_nbr
For XML path(' ')) so( list)

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-21 : 04:27:06

quote:
Originally posted by sodeep

quote:
Originally posted by sodeep

Try like this

Select distinct tt.employee_nbr,Left(so.list,Len(so.list)-1)as skill_nm
From(select to.employee_nbr,t.skill_nm from Tskill t inner join tempskill to
On t.skill_nbr = to. Skill_nbr)tt cross apply
(select skill_nm + ',' as [text()] from Tskill r inner join tempskill z
On r.skill_nbr=z.skill_nbr
Where z.employee_nbr=tt.employer_nbr
For XML path(' ')) so( list)

Yes i've tried this.. But got the same error..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-21 : 10:24:49

Are you using SQL 2005? Change table to your original tablename?

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-22 : 00:28:48

Thanks a Lot Sodeep

I tried something like this... it came

SELECT E.Employee_nbr, Skills
FROM (SELECT DISTINCT Employee_nbr,
(SELECT Skill_Nm+',' AS 'data()'
FROM tskill S2
INNER JOIN temployee_skills E2
ON S2.Skill_nbr= E2.Skill_nbr
WHERE E2.Employee_nbr = E.Employee_nbr
AND S2.Active_FG=0 AND E2.Active_FG=0
FOR XML PATH('')) Skills
FROM temployee_skills E)E

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 02:25:41

SELECT es.employee_nbr,sk.skills FROM
(SELECT DISTINCT employee_nbr FROM empskills) es
CROSS APPLY(SELECT STUFF((SELECT ','+Skill_nm FROM skill INNER JOIN empskills ON (skill.skill_nbr = empskills.skill_nbr) and empskills.Employee_nbr = es.employee_nbr FOR XML PATH('')),1,1,'' ) AS 'skills') sk

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 03:33:40

select employee_nbr, Skill_nm into #temp
from empskills es inner join skill as s on s.skill_nbr = es.skill_nbr

select distinct s.employee_nbr, STUFF((select ','+ skill_nm from #temp where employee_nbr = s.employee_nbr FOR XML PATH('')),1,1,'' ) AS 'skills'
FROM
#temp s

drop table #temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-24 : 05:35:49

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources