May 18, 2011 at 1:56 am
I asked a question a few weeks ago about pivoting data. I read the answers and the article by Jeff Moden that someone suggested - but I still don't get how to do it.
Here's the scenario. I have tblUsers that contains a list of people and tblSkills that contains a list of skills. At this stage there is no join - no connection - between the tables.
CREATE TABLE #tblUsers
(
UserID int,
UserName varchar(50)
)
GO
INSERT INTO #tblUsers
(UserID, UserName)
SELECT 1, 'Bill' UNION ALL
SELECT 2, 'Jim' UNION ALL
SELECT 3, 'Mary'
GO
CREATE TABLE #tblSkills
(
SkillID int,
Skill varchar(50)
)
GO
INSERT INTO #tblSkills
(SkillID, Skill)
SELECT 1, 'Running' UNION ALL
SELECT 2, 'Jumping' UNION ALL
SELECT 3, 'Skipping'
GO
In the front end I need to display a table with the Names across the top and with the Skills as rows. In each cell (intersection between User and Skill) I need to be able to retrieve the UserID and Skill ID.
So, I need data returned to the front end that will look like this.
Skill_________Bill_____Jim_____Mary
Running_____1|1_____1|2_____1|3
Jumping_____2|1_____2|2_____2|3
Skipping_____3|1_____3|2_____3|3
If I can get the data like this I can present a table with a checkbox in each cell that intersects User and Skill and assign the SkillID | UserID value to it. When someone ticks the checkbox I can then easily retrieve that e.g. UserID 2 has SkillID 3 etc.
I need to do this with a dynamic cross tab as the test server is running SQL 2000 - so, can't use pivot. The number of Users and Skills may vary - one time I may be required to select 10 users from tblUsers and 6 skills from tblSkills - next time it might be 5 users and 10 skills etc.
Can anyone show me please how to return the data like that? Thanks very much for any help. I've spent hour after hour on it and have got nowhere.
Edit:
This returns the data I'm after but I need to rotate the columns and rows.
SELECT #tblUsers.UserName, #tblSkills.Skill, Cast(#tblUsers.UserID AS Varchar) + '|' + Cast(#tblSkills.SkillID as Varchar) AS [UserSkill] FROM #tblUsers
CROSS JOIN #tblSkills
WHERE #tblUsers.UserID < 3
AND #tblSkills.SkillID < 3
I stuck a WHERE clause on there just to make it dynamic - so that Select returns 2 users and 2 skills. But it could be 10 users and 6 skills or 4 users and 8 skills etc.
May 18, 2011 at 10:58 am
This should get you the data in the format you want:
SELECT Skill, [Bill], [Jim], [Mary]
FROM (SELECT s.Skill,
s.SkillID,
[Bill] = MAX(CASE WHEN u.UserName = 'Bill' THEN CONVERT(VARCHAR(10), s.SkillID) + '/' + CONVERT(VARCHAR(10), u.UserID) ELSE NULL END),
[Jim] = MAX(CASE WHEN u.UserName = 'Jim' THEN CONVERT(VARCHAR(10), s.SkillID) + '/' + CONVERT(VARCHAR(10), u.UserID) ELSE NULL END),
[Mary] = MAX(CASE WHEN u.UserName = 'Mary' THEN CONVERT(VARCHAR(10), s.SkillID) + '/' + CONVERT(VARCHAR(10), u.UserID) ELSE NULL END)
FROM @tblSkills s
CROSS JOIN @tblUsers u
GROUP BY s.Skill, s.SkillID) s
ORDER BY s.SkillID
Now, you need to make it dynamic. Please read the Cross Tab / Pivot Tables, Part 2 link in my signature for how to do that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 18, 2011 at 12:08 pm
Thanks for that Wayne - seeing that bit done on my own data - I begin to 'get it'.
I'll look at your link and see if I can get it working dynically.
Thanks again.
May 18, 2011 at 1:48 pm
No problem... always glad to help turn the light on!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 19, 2011 at 2:56 am
I've read the article you suggested and think I am beginning to get it. I have a Users table with about a 1000 users in it - I'll only be doing this for up to a maximum of 10 users - based on various parameters - so I propose to start off by sticking those users into #tblUsers.
Then, looking at the code you posted I propose to do the following.
SELECT Skill, [Bill], [Jim], [Mary]
Put that bit of code into @SQL1 and create the 'columns' ([SomeUser1],[SomeUser2] etc) by using Coalesce to concatenate the UserNames in my #tblUsers
FROM (SELECT s.Skill,
s.SkillID,
Put that into @SQL2
[Bill] = MAX(CASE WHEN u.UserName = 'Bill' THEN CONVERT(VARCHAR(10), s.SkillID) + '/' + CONVERT(VARCHAR(10), u.UserID) ELSE NULL END),
[Jim] = MAX(CASE WHEN u.UserName = 'Jim' THEN CONVERT(VARCHAR(10), s.SkillID) + '/' + CONVERT(VARCHAR(10), u.UserID) ELSE NULL END),
[Mary] = MAX(CASE WHEN u.UserName = 'Mary' THEN CONVERT(VARCHAR(10), s.SkillID) + '/' + CONVERT(VARCHAR(10), u.UserID) ELSE NULL END)
Put that into @SQL3 and generate it by again using Coalesce to concatenate the UserNames from #tblUsers and build the case statements
FROM @tblSkills s
CROSS JOIN #tblUsers u
GROUP BY s.Skill, s.SkillID) s
ORDER BY s.SkillID
put that code into @SQL4
Am I heading in the right direction?
As I'm sure you've realised - I've never really used dynamic SQL before - how do I handle the fact that my UserNames might have an apostrophe in them?
May 19, 2011 at 11:16 am
sku370870 (5/19/2011)
Am I heading in the right direction?
Looks right to me.
As I'm sure you've realised - I've never really used dynamic SQL before - how do I handle the fact that my UserNames might have an apostrophe in them?
For when they are the column names, use QuoteName to encase them in [].
For evaluating to see if the column value = that name, encase them with QuoteName(Column, char(39)). This will encase them with quotes, and double-up any existing apostrophes.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply