SQL query to display db data

Question

I have the following database table:

Answer

MemberID | QuestionNo  | AnswerNo   | AnswerString
10       | 1           |     2      | q1 anwer2
10       | 2.1         |     3      | q2.1 answer3
10       | 2.2         |     5      | q2.2 answer5
10       | 7           |     1      | q7 answer 7
11       | 1           |     3      | q1 anwer 3
11       | 3           |     1      | q3 answer 1
11       | 5           |     4      | q5 anwer 4

Each member answers different set of questions based on the answers of previous questions. I want to show the answer in the following format

MemberID | 1  | 2.1 | 2.2 | 3   | 5  |  7
10       | 2  |  3  | 5   |NULL |NULL| 1
11       | 3  |NULL |NULL | 1   | 4  |NULL

Can I do it only in SQL Server 2005? Or I need to use ASP.net to process it?

Answer 1

I think the answer is no, because you're trying to run the values of the QuestionNo column across your display. So I don't think you'll be able to create an SQL query to represent the data in this fashion.

That said, any client program to re-present the data in this fashion should be trivial as you're just transposing data. You wouldn't necessarily need a .NET program. A simple script should suffice.

(Thinking about it a bit more, if you wanted to use T-SQL with its additional flow control, string operations, cursors, etc. you could probably do it. But this sort of scripting would be easier to do in a client program.)

EDIT: Hadn't come across PIVOT before (see other answers). Looks like it will help, but requires a bit of work.

Answer 2

You're looking to pivot data - change columnar data into rows. The old school way is to use CASE statements - as of SQL Server 2005 you can use the PIVOT command. I'll leave it to someone else to provide the PIVOT example.

SELECT t.memberid,
       CASE WHEN t.questionno = 1 THEN t.answerno ELSE NULL END AS 1,
       CASE WHEN t.questionno = 2.1 THEN t.answerno ELSE NULL END AS 2.1,
       CASE WHEN t.questionno = 2.2 THEN t.answerno ELSE NULL END AS 2.2,
       CASE WHEN t.questionno = 3 THEN t.answerno ELSE NULL END AS 3
       CASE WHEN t.questionno = 5 THEN t.answerno ELSE NULL END AS 5
       CASE WHEN t.questionno = 7 THEN t.answerno ELSE NULL END AS 7
  FROM ANSWER t

It's not clear to me what the data type of the questionno column is, update to suit if necessary.

If users can define their own questions, you have to use dynamic SQL. You'll need to get a list of questionno's first, and then construct the CASE statements based on those results. Likewise for PIVOT...

DECLARE @SQL nvarchar(4000)
DECLARE @questionno [data type here]

SET @SQL = 'SELECT t.memberid,'

DECLARE c1 CURSOR READ_ONLY FOR
  SELECT t.questionno
    FROM ANSWER t
GROUP BY t.questionno
ORDER BY t.questionno

OPEN c1

FETCH NEXT FROM c1 INTO @questionno 

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @SQL = @SQL + ' CASE WHEN t.questionno = '+ @questionno +' THEN t.answerno ELSE NULL END AS '+ @questionno','

  FETCH NEXT FROM c1 INTO @questionno 
END

CLOSE c1
DEALLOCATE c1

SET @SQL = @SQL + 'NULL FROM ANSWER t '

EXEC(@SQL)

The NULL FROM... is because I'm too lazy to get rid of the comma that would come from the last CASE statement.

Answer 3

You need to use a pivot query. Microsoft has examples here and there are plenty more to be found on Google.