 | |
Combine multiple records into single row
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Combine multiple records into single row -
03-30-2007
, 04:50 PM
|
You are correct in that the information is coming from different tables. The people "upstairs" have been using a web-based survey system that dumps the results into SQL. The problem, is that I am trying to run reports on some of the information and the way the data is laid out is making it difficult. For example, the survey in question is a short 4 question survey with 2 radio selection groups and then there are 2 text field entries. I am only concerned with the 2 radio selection questions. Question 1 has 4 radio options and question 2 has 5. The problem here is that when the system submits the data to the DB, it creates a record for EACH answer on the survey. For example, the VoterAnswers table will have the VoterID, AnswerText, etc... for each question on the survey with the VoterID remaining the same. This is what is looks like: VoterID AnswerText AnswerID 5 Comments here 2058 5 <NULL> 2057 5 <NULL> 2059 AnswerID is found in the Answer table which corresponds (in this case) to 2057="Technology" and 2059="Satisfied" What I need is one recordset with the following format. VoterID Department Rating ---------- ----------------- ------------ 5 Technology Satisfied Does this clear anything up? |
rating?
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#12
| |||
| |||
|
#13
| |||
| |||
|
|
But how to which know which row is the department and which is the rating? -- Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text - - Show quoted text - |
#14
| |||
| |||
|
|
The AnswerID corresponds to the Department. One row for each. To make is simple, it essentially has only the AnswerID and the AnswerText. |
VoterID AnswerText AnswerID
5 Comments here 2058
5 <NULL> 2057
5 <NULL> 2059
Then you said:
AnswerID is found in the Answer table which corresponds (in this case)
to 2057="Technology" and 2059="Satisfied"
And you want this output:
VoterID Department Rating
---------- ----------------- ------------
5 Technology Satisfied
Somehow we need to magicaly know that answer id 2057 is a department and
that 2059 is a rating. But how do we know that?
There is a general recommedation for this type of questions, and that
is you post:
o CREATE TABLE statements for your table(s).
o INSERT statements with sample data.
o The desired output given the sample.
That makes it possible to copy and paste to develop a tested solution.
Without that, what you get is more or less guessworks. Since you have
already refuted one guess, I don't really feel like playing the game.
I'm sorry, if I'm long-winded, but the bottom line is that to get accurate
help in these forums you must be able to explain your problem so that
others can understand it.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.