We help IT Professionals succeed at work.

General question about crosstab queries

171 Views
Last Modified: 2011-10-19
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.
Untitled-1.gif
Comment
Watch Question

EugeneZSQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
Yes you can:
try to do
Crosstab queries using PIVOT in SQL Server 2005
http://www.mssqltips.com/tip.asp?tip=1019 
http://msdn.microsoft.com/en-us/library/ms177410.aspx 
CERTIFIED EXPERT

Author

Commented:
Hi.

Yes I've read a few tutorials on this.  There are two problems though that I can't figure out:

  1.  The columns must be manually defined in the PIVOT command (i.e.   FOR SkillLevel IN ([Touch Typing], [Microsoft Excel]).  There will be a potentially different number of skills every time this report is run, as different staff can have different skills allocated to them.

  2.  I don't want to perform any maths function on the skill levels (e.g. SUM).  I just want to show the values from tblSkillLevels.  Again the only examples I can find use either SUM or AVERAGE, which is not required
CERTIFIED EXPERT

Author

Commented:
As above:

  >> FOR SkillLevel IN ([Touch Typing], [Microsoft Excel]

I need to reference these dynamically if possible, e.g.

        FOR SkillLevel IN (SELECT SkillLevel FROM tblSkillsUsers)

but it doesn't work (gives syntax error)
EugeneZSQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
Can you post some test data with tables structure that you would like to use?
CERTIFIED EXPERT

Author

Commented:
I've generated the sql to create the table structure and data.  Its in the attached file.

Afterwards, if you run the following query you will see a list of user's skill levels.  Now I'd like to display this in a matrix style display (see image attachment in original post).

As I said before, PIVOT looks promising except for the fact that values have to be explicitly defined (e.g. every skill individually to form the pivot table's columns).  Because the number of users is ever growing, I need a way to automatically select all user values.

My head hurts... :-/
SELECT
	s.skillTitle,
	u.firstname, 
	u.surname, 
	sl.skillLevel
FROM
	tblSkillLevels sl INNER JOIN
	tblSkillsUsers su ON sl.skillsUserID = su.ID INNER JOIN
	tblSkills s ON su.skillID = s.ID INNER JOIN
	tblUsers u ON su.userID = u.ID
ORDER BY
	s.skillTitle

Open in new window

create.txt
EugeneZSQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
try it

select firstname, max([Microsoft Excel]) [Microsoft Excel],
MAX([Microsoft Word]) [Microsoft Word],
MAX([Microsoft OneNote]) [Microsoft OneNote],
MAx([Microsoft Outlook]) [Microsoft Outlook],
max([Microsoft PowerPoint]) [Microsoft PowerPoint],
max([Touching Typing])[Touching Typing]
from(
select firstname, 
case when skilltitle='Microsoft Excel' then max(skilllevel) end [Microsoft Excel],
case when skilltitle='Microsoft OneNote' then max(skilllevel) end [Microsoft OneNote],
case when skilltitle='Microsoft Outlook' then max(skilllevel) end [Microsoft Outlook],
case when skilltitle='Microsoft PowerPoint' then max(skilllevel) end [Microsoft PowerPoint],
case when skilltitle='Microsoft Word' then max(skilllevel) end [Microsoft Word],
case when skilltitle='Touching Typing' then max(skilllevel) end [Touching Typing]
 from  
(
SELECT
	s.skillTitle,
	u.firstname, 
	u.surname, 
	sl.skillLevel
--into tbl_test
FROM
	tblSkillLevels sl INNER JOIN
	tblSkillsUsers su ON sl.skillsUserID = su.ID INNER JOIN
	tblSkills s ON su.skillID = s.ID INNER JOIN
	tblUsers u ON su.userID = u.ID
 
) z
 
 
group by firstname,skilltitle)a 
group by firstname
--select * from tbl_test

Open in new window

CERTIFIED EXPERT

Author

Commented:
Hi

Sorry its taken so long to reply, I actually thought I had replied already.... [duh]

Your code does work, but its not what I need.  I need a way to not have to manually type all the skills into the SQL statement.  For example, the code should automatically generate the list of skills.  After reading the documentation, I don't think it is possible without using dynamic SQL, which I don't want to do...
EugeneZSQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.