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 2000 Forums
 Transact-SQL (2000)
 Pivot table but ???

Author  Topic 

Limbic
Starting Member

15 Posts
Posted - 2006-05-17 : 03:19:09
Salute mates,

So, this quiz stuff doesn’t let me go.

I was able to create a pivot table with all the questions in the quiz and with all employees that participated.
Everything works so far (at least I hope so).
Here is what I have

employeeId | Question1 | Question2 | Question 3 | ……..
    1		3	    1	        0
    2		0	    2	        1

Quick explanation, employee 1 answered question1 3 times correctly, question2 1 time but question3 was never answered correctly.
Maybe you know right know what comes next in a customers mind, yes, he wants to see how many times an employee failed a question.
So it should look like this

employeeId | Correct1 , Wrong1 | Correct2, Wrong2 | Correct3, Wrong3 | ……..
    1		3	  0	    1	    0		0	2
    2		0	  2	    2	    1		1	0

Here one can see that employee1 answered question3 2 times wrong.

Here is the DML and DDL for my solution of the simple pivot,

-- Every attempt of an employee whether right or wrong comes in this table
CREATE TABLE quizsummary
(
	employeeId INT,
	questionId INT,
	IsCorrect INT
)
--- All questions are stored in this table, responsible for the columns in the pivot table
CREATE TABLE questions
(
	questionId INT,
	[text]	NVARCHAR(15)
)

-- Data for the questions table, one question(500) is NOT answered by any employee 

INSERT INTO questions(questionId, [text])
VALUES(10,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(20,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(30,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(40,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(50,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(60,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(70,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(80,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(90,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(100,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(110,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(120,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(130,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(500,'What is..?')

INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(4,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(3,20,0)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,20,0)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,20,0)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,20,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,20,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,30,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,30,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,40,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,40,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,50,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,50,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,60,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,60,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,70,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,70,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,80,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,80,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,90,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,90,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,100,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,100,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,110,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,110,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,120,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,120,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,130,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,130,1)

DECLARE @select varchar(8000)
DECLARE @sumfunc varchar(100)
DECLARE @pivot varchar(100)
DECLARE @table varchar(100) 

SET @SELECT = 'SELECT employeeId FROM quizsummary GROUP BY employeeId '
-- Replace with employee select
SET @sumFunc = 'count(employeeId)'
-- replace with surname or unit name
SET @pivot = 'questionId'
-- replace with 
SET @table = 'questions'


DECLARE @sql varchar(8000), @delim varchar(1)

-- Create the pivot table
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 AND LEN('+@pivot+') > 0 ')

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 + ' ') 
-- stuff Deletes a specified length of characters and inserts another set of characters at a specified starting point.



EXEC (@select)




Has anyone some ideas how to double the columns of a question and to display the wrong attempts?


Limbic

Limbic
Starting Member

15 Posts
Posted - 2006-05-18 : 04:29:41
So,
I could figure something out by myself, as though I don't think it's a representable solution I'll post it. 


CREATE TABLE quizsummary
(
	employeeId INT,
	questionId INT,
	IsCorrect INT
)
--- All questions are stored in this table, responsible for the columns in the pivot table
CREATE TABLE questions
(
	questionId INT,
	[text]	NVARCHAR(15)
)

-- Data for the questions table, one question(500) is NOT answered by any employee 

INSERT INTO questions(questionId, [text])
VALUES(10,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(20,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(30,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(40,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(50,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(60,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(70,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(80,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(90,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(100,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(110,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(120,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(130,'What is..?')
INSERT INTO questions(questionId, [text])
VALUES(500,'What is..?')

INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(4,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(3,20,0)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,10,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,20,0)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,20,0)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,20,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,20,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,30,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,30,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,40,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,40,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,50,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,50,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,60,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,60,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,70,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,70,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,80,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,80,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,90,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,90,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,100,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,100,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,110,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,110,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,120,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,120,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(1,130,1)
INSERT INTO quizsummary(employeeId,questionId,IsCorrect) VALUES(2,130,1)

/* Create a temporary table to modify questionId

*/
DROP TABLE #temp_quizsummary
CREATE TABLE #temp_quizsummary
(
	employeeId INT,
	question NVARCHAR(50)                                    
)

INSERT INTO #temp_quizsummary
SELECT employeeId, question = CASE WHEN IsCorrect = 0 THEN
			 	CAST(questionId AS NVARCHAR) + '_W'
			ELSE 
				CAST(questionId AS NVARCHAR) + '_C'
			END
FROM quizsummary

CREATE TABLE #temp_questions
(
	question NVARCHAR(50)
)


INSERT INTO #temp_questions (question)

SELECT CAST(questionId AS NVARCHAR) + '_W' AS question FROM questions
UNION ALL
SELECT CAST(questionId AS NVARCHAR) + '_C' AS question FROM questions

SELECT * FROM #temp_questions
SELECT * FROM #temp_quizsummary

DECLARE @select varchar(8000)
DECLARE @sumfunc varchar(100)
DECLARE @pivot varchar(100)
DECLARE @table varchar(100) 

SET @SELECT = 'SELECT employeeId FROM #temp_quizsummary GROUP BY employeeId '
-- Replace with employee select
SET @sumFunc = 'count(employeeId)'
-- replace with surname or unit name
SET @pivot = 'question'
-- replace with 
SET @table = '#temp_questions'


DECLARE @sql varchar(8000), @delim varchar(1)

-- Create the pivot table
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 AND LEN('+@pivot+') > 0 ')

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 + ' ') 
-- stuff Deletes a specified length of characters and inserts another set of characters at a specified starting point.



EXEC (@select)






But if someone has a simplied version don't hesitate
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site