Tabulation of poll results into reportable format

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

General SQL Server Forums

New to SQL Server Programming

Tabulation of poll results into reportable format

Author

Topic

mobardev
Starting Member

2 Posts

Posted - 2010-04-20 : 18:49:46

EDIT: I reformatted to improve readability of tables.

This is probably so simple but for some reason I seen to be having a hard time wrapping my head around it.

Be forewarned I am definitely still in the learning stages of more advanced SQL. I have been in the IT field for quite a long time but it was spent in the general practitioner field (i.e. IT Coordinator/Manager for SMB's). I now have the opportunity to dig a little more on the development side - I like it but it can be a painful process.

Process is that members log on to a web site to evaluate "employees". The employee is then rated 1 thru 5 (blanks are accepted and are listed as no response/NULL in table) on 11 different questions. Each record in the table is a separate evaluation. Basically you get the evaluation data as displayed below.

DATA:


EmployeeQ1	Q2	Q3	Q4	Q5	Q6	Q7	Q8	Q9	Q10	Q11
1	5	5	5	5	5	5	5	5	5	5	5
1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	5	NULL
1	5	5	5	5	5	5	5	5	5	5	5
1	4	5	5	5	5	3	3	5	1	5	5
1	5	5	5	5	5	5	5	5	5	5	5
1	3	3	3	3	3	3	3	3	3	3	3
2	5	5	5	5	5	5	5	5	5	5	5
2	4	4	4	4	4	4	4	4	4	5	4
2	4	5	5	4	4	4	5	4	4	4	4
2	5	5	5	5	5	5	5	5	5	5	5
2	5	5	5	5	5	5	5	5	5	5	5
2	5	5	5	5	5	5	5	5	5	5	5
3	5	5	5	5		5	5	5	NULL	NULL	NULL
3	5	5	5	5	5	5	5	5	5	5	5
3	5	5	5	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	4	4	4	4	3	5	4	1	5	4	4
3	5	5	5	5	5	5	5	5	5	5	5
3	5	5	5	5	5	4	5	5	5	5	5

Sounds pretty straight forward hopefully. Now I want the data out of the table in a more presentable format - a single page report for each evaluated employee.

DESIRED FORMAT:


Emp = 	1											
	Blank	1	2	3	4	5	 Average
Q1	1	0	0	1	1	3	4.4
Q2	1	0	0	1	0	4	4.6
Q3	1	0	0	1	0	4	4.6
Q4	1	0	0	1	0	4	4.6
Q5	1	0	0	1	0	4	4.6
Q6	1	0	0	2	0	3	4.2
Q7	1	0	0	2	0	3	4.2
Q8	1	0	0	1	0	4	4.6
Q9	1	1	0	1	0	3	3.8
Q10	0	0	0	1	0	5	4.7
Q11	1	0	0	1	0	4	4.6

I was trying (and failing) to create a SQL SELECT statement that would output:


EmpQuestionBlank_Count1_Count2_Count3_Count4_Count5_Count
1	1	1	0	0	1	1	3
1	2	1	0	0	1	0	4
1	3	1	0	0	1	0	4
1	4	1	0	0	1	0	4
1	5	1	0	0	1	0	4
1	6	1	0	0	2	0	3
1	7	1	0	0	2	0	3
1	8	1	0	0	1	0	4
1	9	1	1	0	1	0	3
1	10	0	0	0	1	0	5
1	11	1	0	0	1	0	4
2	1	you get the idea…..					
2	2						
2	3						
2	4						
2	5

I am somewhat familiar with Crystal and Reporting Services so I am more looking for assistance with the SQL side and an understanding of the proper method to do this. Am I way off base here on the technique? Any suggestions/help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:35:36

seems like what you're after is UNPIVOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources