troubleshooting Question

General question about crosstab queries

Avatar of Rouchie
RouchieFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005Microsoft SQL Server
9 Comments1 Solution177 ViewsLast Modified:
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?

Thanks in advance.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros