String Concatenation

Question

String Concatenation

swathi.g

SSC Journeyman

Points: 99
More actions
December 19, 2008 at 9:54 pm

#125292

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Please post your table structure and sample data the way I do here in the future. See the article in my signature on How to post sample data if you need more information on how to do this.

This is an example using FOR XML PATH.

[font="Courier New"]

DECLARE @Employee TABLE(

Employee_nbr INT,

Skill_nbr INT)

INSERT INTO @Employee

SELECT 1, 1 UNION ALL

SELECT 1, 2 UNION ALL

SELECT 2, 3 UNION ALL

SELECT 2, 2 UNION ALL

SELECT 3, 1

DECLARE @Skills TABLE(

Skill_nbr INT,

Skill_name VARCHAR(20) )

INSERT INTO @Skills

SELECT 1, '.net' UNION ALL

SELECT 2, 'sql' UNION ALL

SELECT 3, 'vb'

SELECT E.Employee_nbr, Skills

FROM (SELECT DISTINCT Employee_nbr,

( SELECT Skill_Name AS 'data()'

FROM @Skills S2

INNER JOIN @Employee E2 ON S2.Skill_nbr= E2.Skill_nbr

WHERE E2.Employee_nbr = E.Employee_nbr

FOR XML PATH('')) Skills

FROM @Employee E) E[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

This type of denormalization is not normally a good thing to do in a database... why do you need to do this? What is the purpose?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

swathi.g SSC Journeyman Points: 99 More actions · Answer 3

Thanks A Lot For ur Reply,,,,,,

This is really what i wanted.

I wanted this as a PArt of Stored Procedure for a Report GEneration.

Thanks Again.... :):):):):)