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.
| Author |
Topic |
|
vfxd
Starting Member
1 Post |
 Posted - 2012-08-19 : 21:38:03
|
Hi, I have 3 tables, Table Person, Table Skill and Table JoinPersonSkill (many-to-many).The idea is that a 'Person' can have many 'Skills'.The JoinPersonSkill table also has a field for SkillLevel (e.g. beginner 1, novice 2, expert 3, etc).I would like to do a select where the resulting recordset has the PersonID & Name in each record, and a *FIELD* for each skill which shows their SkillLevel.So the result might look like this (using a generic example):PersonID Name Basketball Soccer Volleyball1 Adam 3 1 NULL2 Beth 2 2 13 Charlie 2 NULL 24 Doug 1 1 15 Emily 3 3 3 (There is a maximum of 50 Skills so I'm not worried about creating a result with an unsupported # of fields.)Is there a SQL statement that could join the tables & give a recordset like the table above? Can you provide an example? Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-19 : 21:46:30
|
yep. you could do a logic likeSELECT p.PersonID,p.Name,MAX(CASE WHEN s.Name = 'Basketball' THEN ps.SkillLevel END) AS Basketball,MAX(CASE WHEN s.Name = 'Soccer' THEN ps.SkillLevel END) AS Soccer,MAX(CASE WHEN s.Name = 'Volleyball' THEN ps.SkillLevel END) AS VolleyballFROM Person pINNER JOIN JoinPersonSkill psON ps.PersonID = p.PersonIDINNER JOIN Skill sON s.SkillID = ps.SkillIDGROUP BY p.PersonID,p.Name if you want to make skills dynamic usehttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspxalso i've assumed columnnames in above statement so make sure you use actual columns instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
join many-to-many, with field for each record