显示数据库数据的 SQL 查询

本文介绍了显示数据库数据的 SQL 查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下数据库表:

回答

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来处理?

Can I do it only in SQL Server 2005? Or I need to use ASP 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.