Dynamic Cross tab (without aggregation?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic Cross tab (without aggregation?)

Author

Topic

Webskater
Starting Member

19 Posts

Posted - 2011-05-18 : 10:35:44

I need to pivot some data - but can't use pivot because the test server is SQL Server 2000. I need to use a dynamic cross tab query but am too thick to understand 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 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.

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 added a WHERE clause on there just to make it dynamic - so that this particular Select returns 2 users and 2 skills. But it could be 10 users and 6 skills or 4 users and 8 skills etc.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 10:44:05

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 11:12:20

I forgot to add, you can use MIN or MAX as the aggregate function, it will accomplish what you're looking for.

Webskater
Starting Member

19 Posts

Posted - 2011-05-18 : 11:14:56

Thanks for your reply. I have had a look through your SP but, to be honest, it's over my head.

Taking the code I posted above ... how would I call your procedure?

EXECUTE crosstab SELECT statement, summary calculation, pivot column, table name

I assume the 'SELECT statement' would be ...
DECLARE @SelectStatement varchar 8000
SET @SelectStatement = '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'

(would the inverted commas in the Select statement in the field where I am concatenating UserID + '|' + SkillID cause a problem?

But what would I use for 'summary calculation'? Do I have a summary calculation? I just want to transpose rows and columns?

Which column is the 'pivot column'?

Which 'table name' should be passed to Parameter 4?

Apologies for daftness of questions ... if I could see how to put my data into your Stored Procedure and get the data back with rows and columns transposed - I might be able to get to understand it.

Thanks for your reply and help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 11:26:51

This gives the results you requested:

EXEC crosstab 'SELECT Skill FROM #tblSkills S CROSS JOIN #tblUsers U GROUP BY Skill', 
'MAX(CAST(U.UserID AS varchar)+''|''+CAST(S.SkillID as varchar))', 
'U.UserName', 
'#tblSkills S CROSS JOIN #tblUsers U'

You need to create the crosstab stored procedure in the same database.

Webskater
Starting Member

19 Posts

Posted - 2011-05-18 : 11:51:28

Thanks again for your reply.

When I tried to create your stored procedure in my database ...

CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' +
 @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

When I try to run it it says:
Msg 156, Level 15, State 1, Procedure crosstab, Line 27
Incorrect syntax near the keyword 'pivot'.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 12:00:59

Use this version:

CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS _pivot INTO ##_pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##_pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##_pivot' AND column_name='_pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), _pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' +
 @pivot + ' WHEN ' + @delim + convert(varchar(100), _pivot) + @delim + ' THEN ' ) + ', ' FROM ##_pivot

DROP TABLE ##_pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

You must be on a SQL 2005 or higher server, and your database is in 8.0 compatibility mode?

Webskater
Starting Member

19 Posts

Posted - 2011-05-18 : 13:51:24

Okay, got it working - and thanks very much for your help.

One (hopefully last) thing. I want to be able to vary the number of users and skills dynamically.

If I do this ...

EXEC crosstab 'SELECT Skill FROM #tblSkills S CROSS JOIN #tblUsers U WHERE S.SkillID < 3 GROUP BY Skill',
'MAX(CAST(U.UserID AS varchar)+''|''+CAST(S.SkillID as varchar))',
'U.UserName',
'#tblSkills S CROSS JOIN #tblUsers U'

... it does what I want - i.e. only returns Skills with an ID of less than 3.

But, if I do this ...

EXEC crosstab 'SELECT Skill FROM #tblSkills S CROSS JOIN #tblUsers U WHERE U.UserID < 3 GROUP BY Skill',
'MAX(CAST(U.UserID AS varchar)+''|''+CAST(S.SkillID as varchar))',
'U.UserName',
'#tblSkills S CROSS JOIN #tblUsers U'

... it still returns all users but puts in null values for the User whose UserID is 3.

Webskater
Starting Member

19 Posts

Posted - 2011-05-18 : 13:53:23

Sorry, can't edit replies ...

So, if I restrict the SkillID as shown above it returns:
Skill_________Bill_____Jim_____Mary
Running_______1|1______1|2_____1|3
Jumping_______2|1______2|2_____2|3

if I restrict the UserID as shown above it returns:
Skill_________Bill_____Jim_____Mary
Running_______1|1______1|2_____null
Jumping_______2|1______2|2_____null
Skipping______3|1______3|2_____null

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 13:54:08

Don't include the WHERE clause. The example I posted will work with any number of skills and users, as long as the generated SQL doesn't exceed 8000 bytes.

Webskater
Starting Member

19 Posts

Posted - 2011-05-18 : 15:07:28

Hi and thanks again for your reply.

But how do I select which Users and Skills I want to retrieve?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 15:19:40

Assuming you have a regular (non-temp) table of Users and Skills, I'd suggest selecting only the users and skills you want into the temp tables you've posted already, and use the same statement. You can populate them ahead of time like this:

SELECT SkillID, Skill INTO #tblSkills FROM Skills WHERE Skill IN('Running','Jumping')
SELECT UserID, User INTO #tblUsers FROM Users WHERE User IN('Bill','Mary')

EXEC crosstab ...

Webskater
Starting Member

19 Posts

Posted - 2011-05-18 : 15:55:59

Okay, got the idea and thanks once more.

One last thing ... is this an efficient way of doing things? Generally I'd be selecting between 4 and 10 Users into #tblUsers and between 20 and 40 Skills into the #Skills table - from a Users table with about 1000 rows and a Skills table with about 100 rows.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 15:59:11

It's probably as efficient as you can do, based on the dynamic nature of the query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources