A database holds records about skills, users and user's skill levels for each skill. Take these 4 tables:
tblUsers - holds the user names
tblSkills - holds the skill names
tblSkillsUsers - many-to-many table that allocates users to skills
tblSkillLevels - stores users skill levels for each skill (i.e. 60%)
I need to create a table of results that shows all users/skills and the users' skill levels for each (see image below).
Currently I do this in SQL2005/ASP.NET manually by pulling out a long list of results then enumerating through the columns and manually building the final result. It works well, but I was wondering if there was a better way to achieve this natively in SQL 2005? I've read about crosstab queries but the only examples I see have the columns hard coded into the statement. This is no good in this instance because I will never know how many users/skills there are.
Does anyone know of a way to achieve the results below using T-SQL?
try to do
Crosstab queries using PIVOT in SQL Server 2005