sql server programming: If you do a google search on sql 2000 pivot you will find a lot of good info
regarding this but my first thought was something like this:

SELECT SUM(CASE WHEN [Answer] = 5 THE 1 ELSE 0 END) AS [SA]
,SUM(CASE WHEN [Answer] = 4 THE 1 ELSE 0 END) AS [A]
....
GROUP BY [Question]


--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Using MS-SQL2000. I'm creating a Web form with 10 questions. Each question
can be answered by selecting one of 5 radio buttons (strongly agree, agree,
neutral, disagee, strongly disagree). Once all the questions are answered the
form is submitted to the database. I need help on how to structure the
database.

I need to be able to report results as

|Question| |SA| |A| |N| |D| |SD|
Question 1 5 2 2 1 3
Questiion 2 3 1 3 2 1

I tried creating the table with each question being a column, but since
SQL2000 doesn't support pivot I need another structure?

Your help is greatly appreciated. Thanks

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200706/1

Thanks Andrew I really appreciate your response. In my first attempt to
create the survey table each question is column. Table is
Evaldate
Name
Q1
Q2
Q3
Q4
so I can't group by question. That's why I think that table structue won't
work. Any ideas? Thanks again
[quoted text, click to view]

--
Message posted via http://www.sqlmonster.com

[quoted text, click to view]

I think you give voice to what I call RS ('Radical Storage') borrowing
the idea from Skinners 'Radical Behaviorism'. This says there is one
primary purpose for which the data is intended. In most cases the sole
purpose is an operation on the data, ie. crosstabulation. The scheme for
the storage of the data is completely specified by the nature of the 'feed'
required by the operation. In RS there is no 'design' phase in development
as it is completely replaced by a 'search' phase for the appropriate
operation. While many might see this as an unburdening from the
traditional requirement of the logical consistency (ie integrity) of the
data,
it is only one set of problems replacing another. While 'data design' is
replaced by 'data storage' the concept of looking at the database as
a 'sole' provider persists. The habit of looking at the db as the
the singular source for integrity resurfaces in the form of a presumption
that it will offer the operation and storage for the data. The origin of
this habit is its own topic but the net result is that most developers,
regardless of orientation, have a very good chance of winding up in the same
place.
As the saying goes, there is no such thing as a free feed.

www.beyondsql.blogspot.com

I agree totally with Hugo. You need to normalize that table to make it work
properly.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

[quoted text, click to view]

Hi Chamark,

Indeed, this table structure is not a good design. Try something like
this:

CREATE TABLE Answers
(Evaldate datetime NOT NULL,
Name varchar(60) NOT NULL,
QuestionNo tinyint NOT NULL,
Answer char(2) NOT NULL,
CONSTRAINT PK_Answers PRIMARY KEY (Evaldate, Name, QuestionNo),
CONSTRAINT CK_QuestionNo CHECK (QuestionNo BETWEEN 1 AND 10),
CONSTRAINT CK_Answer CHECK (Answer IN ('SA', 'A', 'N', 'D', 'SD'))
);

In the real table, you'd want to add foreign key constraints to the
Questions table and the Respondents table (if you have one) as well.

--
Hugo Kornelis, SQL Server MVP