SQL Server:多行合二为一的高级报表 [英] SQL Server: advanced report of multiple rows into one

问题描述

我的代码将许多表格中的数据汇总在一起，这些数据涉及用户对特定课程的所有问题的回答.回复如下:

I have code that puts together data from many tables regarding a users response to all questions of a specific lesson. Responses look like this:

userid|lesson|question |response|label|weight|duration_seconds
========================================================================
bob   |first |loc_nameA|4       |R9   |3.5   |189
bob   |first |loc_nameB|2       |R7   |4.5   |113
…

需要制作一份报告，显示一行中显示的所有回复.因此，对于每个问题，我需要将响应及其相应的标签、权重和持续时间显示到一列中，如下所示:

A report needs to be made showing all responses shown in one row. So for each question I need to display the response into a column along with its corresponding label,weight and duration like this:

userid|lesson|1_resp|1_labl|1_weig|1_dura|2_resp|3_labl|3_weig|3_dura|4_resp…
========================================================================
bob   |first |4     |R9    |3.5   |189   |2     |R7    |4.5   |113   |1

OR 或者使用问题"列值作为动态列的一部分姓名.目前，它们都有像 L1Q1 这样的逻辑名称，所以只有 1,2,3 作为列名就足够了，但情况可能并非总是如此:

OR alternatively by using the "question" column value as part of the dynamic column name. Currently they all have logical names like L1Q1 so just 1,2,3 will suffice as column names, but this might not always be the case:

userid|lesson|loc_nameA_resp|loc_nameA_labl|loc_nameA_weig|loc_nameA_dura|loc_nameB_resp|loc_nameB_labl|loc_nameB_weig|loc_nameB_dura|loc_nameC_resp…
================================================================================================================================================
bob   |first |4             |R9            |3.5           |189           |2             |R7            |4.5           |113           |1

我一直在阅读有关数据透视表的内容，但所有示例似乎都比我所描述的更有限.如何使用 SQL Server 2005 完成此操作?我应该使用其他东西吗?有没有更简单的方法?

I have been reading about pivot tables but all examples seem more limited than what I am describing. How can this be done with SQL Server 2005? Should I be using something else? Is there an easier way?

推荐答案

您可以使用动态 SQL 来解决这个问题——或者如果只是为了一组数据而手动编写.在这两种情况下，您最终都会得到如下所示的结果:

You can use dynamic SQL to solve this problem -- or if it is just for one set of data write it by hand. In both cases you are going to end up with something that looks like this:

SELECT R1.userid, R1.lesson, 
       R1.response as loc_nameA_resp, R1.lable as loc_nameA_labl, R1.weight as loc_nameA_weig, R1.duration_seconds as loc_nameA_dura,
       R2.response as loc_nameB_resp, R2.lable as loc_nameB_labl, R2.weight as loc_nameB_weig, R2.duration_seconds as loc_nameB_dura,
--- etc for each question
FROM user U
JOIN response R1 on R1.userid = u.userid and R1.lesson = 'first' and R1.question = 'loc_nameA'
JOIN response R2 on R2.userid = u.userid and R2.lesson = 'first' and R2.question = 'loc_nameB'
--- etc for each question
WHERE
   U.userid = 'bob' -- this does not need to be bob, whatever user you want.

<小时>

给你，测试和一切.

---

Here you go, tested and everything.

DECLARE @sqlSelectList varchar(max);
DECLARE @sqlJoinList varchar(max);

SELECT @sqlSelectList = '', @sqlJoinList='';

WITH Questions AS
(
  SELECT DISTINCT question
  FROM ResultsChoices
)
SELECT -- We use the question as the alias for join uniqueness,
       -- We could increment a number but why bother?
  @sqlJoinList = @sqlJoinList +
     ' JOIN ResultsChoices '+question+' on '+question+'.userid = u.userid and '+question+'.question = '''+question+'''', 
  @sqlSelectList = @sqlSelectList +
     ', '+question+'.response as '+question+'_resp, '+question+'.label as '+question+'_labl, '+question+'.weight as '+question+'_weig, '+question+'.duration_seconds as '+question+'_dura '
FROM Questions;

DECLARE @sql NVARCHAR(max);

SET @sql = N'SELECT DISTINCT u.userid ' + @sqlSelectList + N' FROM #ResultsChoices u ' + @sqlJoinList;

EXEC sp_executesql @sql

这篇关于SQL Server:多行合二为一的高级报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！