Hi all,

this question is _not_ directly exam related, but something I got stuck on today...

Here's an example. I have a database with three tables - tblSkills, tblPeeps and tblJctSkillsPeeps. The last table is used to establish a many to many relationship between peeps and skills. Tables:

tblSkills
ID Text
-- -----
01 SQL
02 ASP
03 IIS

tblPeeps
ID Name
-- ------
01 Joe
02 Jill
03 John

tblJctSkillsPeeps
SkillID PeepID
------- ------
01 01
01 02
02 02
02 03
03 02
03 01
03 03

Now, I want to design a query that gives me the following:
PeepID PeepName Skills
------ -------- ------------
01 Joe 'SQL, IIS'
02 Jill 'SQL, ASP, IIS'
03 John 'ASP, IIS'

In short, I want concatenated contents from one table.

Is this possible in one, single neat query?

Thanks,
J

Report this post to a moderator

How about creating a view for this?

__________________
Andy C
CCNP, CCVP, CCDA, CQS (WLANSE, WLANFE, Unity Support, IPT Design, IPT Express , IPCC Express, IP Communications Express, CallManager Express), JNCIS-M #1027, JNCIS-FWV #311, F5CSE, CCSE NG, MCSE NT4/2K, MCSE:Messaging, ITIL, A+

Report this post to a moderator

I'd prefer not using a view of possible. The main reason for which being that there are a lot of similar issues in the database (which means a _lot_ of views...).

Also, if it's possible to do this in one pretty query, I'd love to know how!

Maybe a function that creates a cursor to do the concatenation and return the concatenated string might do the trick? I have to try this out...

Report this post to a moderator

A derived table query will produce the results you need.

Report this post to a moderator

Could you give a sample query? I can't immediately see how it would be done, see.

Thanks,
J

Report this post to a moderator

If there are really a lot of similar issues in your database (which means a _lot_ of views...) and you really want to do this in one pretty query, Can you denormalize your databse schema? In the price of data redundancy, you definitely only need one simply query to get what you want.

Report this post to a moderator

In msdn, it stated:

denormalize only if you can demonstrate a performance gain (usually by not needing to perform multitable joins for each query) and only if losses in data integrity don't offset the performance gain. Data user convenience alone is never a justification for denormalizing. You can give users what they want by using virtual denormalization techniques, such as creating views and stored procedures.

Report this post to a moderator

You don't need to denormalize. You just have to be willing to pound out some code like the big dogs (such as myself) do for a living.
Foreword: the tables I created were named tblSkills(SkillID INT, SkillName VARCHAR(10)); tblSkillPerson(SkillID INT, PersonID INT); tblPerson(PersonID INT, PersonName VARCHAR(10))

Create the tables listed above and load them with the data you provided from your earlier post.
Then run the script below in Query Analyzer.
I ran it against the tables and it gave the report in the format you are looking for.

Here's the code to do what you need:
----------------------------------------
----------------------------------------
SET NOCOUNT ON

CREATE TABLE #tempPersonReport(
PersonID int,
PersonName varchar(10),
Skills varchar(255)
)

insert into #tempPersonReport
select
PersonID, PersonName, ''
from
tblPerson P


DECLARE @intPersonID INT
DECLARE @strSkillName VARCHAR(20)

DECLARE curPerson CURSOR FOR
SELECT PERSONID FROM #tempPersonReport

OPEN curPerson

FETCH NEXT FROM curPerson INTO @intPersonID

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE curSkills CURSOR FOR
SELECT S.SKILLNAME FROM TBLSKILLPERSON SP
JOIN TBLSKILLS S ON SP.SKILLID = S.SKILLID
WHERE SP.PERSONID = @intPersonID

OPEN curSkills

FETCH NEXT FROM curSkills INTO @strSkillName

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #tempPersonReport
SET SKILLS = (CASE SKILLS
WHEN '' THEN @strSkillName
ELSE SKILLS + ',' + @strSkillName
END)
WHERE PERSONID = @intPersonID

FETCH NEXT FROM curSkills INTO @strSkillName
END

CLOSE curSkills
DEALLOCATE curSkills

FETCH NEXT FROM curPerson INTO @intPersonID
END

CLOSE curPerson
DEALLOCATE curPerson

SELECT * FROM #tempPersonReport

DROP TABLE #tempPersonReport
----------------------------------------
----------------------------------------

In essence, it's a loop within a loop using 2 cursors. I know, I know, always do your best to stay away from cursors, but in this case it is a suffice solution.

Last edited by hard_coder on 05-14-02 at 07:21 PM

Report this post to a moderator

Temp table, eh? Why didn't I think of that? I can see how the cursors might be considered undesirable, but then again - it's a whole lot better than all the extra network time involved in the client calling a stored procedure repeatedly to get the summary for each item...

Thanks a lot,
J

Report this post to a moderator

where do i send the bill? hehe

Report this post to a moderator