显示数据库数据的 SQL 查询 [英] SQL query to display db data

问题描述

我有以下数据库表:

回答

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

我只能在 SQL Server 2005 中执行此操作吗?还是需要用ASP.net来处理?

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

推荐答案

您正在寻找数据透视 - 将列数据更改为行.老派的方法是使用 CASE语句 - 从 SQL Server 2005 开始，您可以使用 PIVOT 命令.我会把它留给其他人来提供 PIVOT 示例.

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

我不清楚 questionno 列的数据类型是什么，如有必要，请更新以适应.

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

如果用户可以定义自己的问题，则必须使用动态 SQL.您需要先获得一个问题列表，然后根据这些结果构建 CASE 语句.同样对于 PIVOT...

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)

NULL FROM... 是因为我懒得去掉最后一个 CASE 语句中的逗号.

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

这篇关于显示数据库数据的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！