|
Home > Archive > SQL server exams > May 2002 > SQL Query Question (non-certificate related)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Query Question (non-certificate related)
|
|
|
| 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 | |
|
| How about creating a view for this? | |
|
| 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... | |
| emulti 2002-04-13, 2:04 pm |
| A derived table query will produce the results you need. | |
|
| Could you give a sample query? I can't immediately see how it would be done, see.
Thanks,
J | |
| amyland 2002-05-14, 11:42 am |
| 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.
 | |
| amyland 2002-05-14, 11:48 am |
| 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. | |
| hard_coder 2002-05-14, 1:12 pm |
| 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. | |
|
| 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 | |
| hard_coder 2002-05-14, 3:27 pm |
| where do i send the bill? hehe |
|
|
|
|