General question about crosstab queries
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
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
ASKER
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
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
ASKER
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)
>> 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)
Can you post some test data with tables structure that you would like to use?
ASKER
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... :-/
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
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_testASKER
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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