如何显示表格行垂直 [英] How to display a table row vertical

问题描述

这是我的陈述。

This is my statement.

SELECT (student.student_Name)AS Leerling, (course.course_Name)AS Vak, AVG(result.Result)AS Cijfer, (SELECT AVG(Result) FROM result WHERE result.student_ID = student.ID)AS Gemiddelde 
FROM course
JOIN test ON course.ID = test.Course_ID
JOIN result ON test.ID = result.test_ID
JOIN student ON result.student_ID = student.ID
GROUP BY student.student_Name,Vak;

这就是我的回报。

结果 - imgbb.com [ ^ ]



这就是我想要的。



result2 - imgbb.com [ ^ ]



我尝试过：

This is what i get back.
Result — imgbb.com[^]

AND this is what i want.

result2 — imgbb.com[^]

What I have tried:

Quote：

这是我的陈述。

This is my statement.

SELECT (student.student_Name)AS Leerling, (course.course_Name)AS Vak, AVG(result.Result)AS Cijfer, (SELECT AVG(Result) FROM result WHERE result.student_ID = student.ID)AS Gemiddelde 
FROM course
JOIN test ON course.ID = test.Course_ID
JOIN result ON test.ID = result.test_ID
JOIN student ON result.student_ID = student.ID
GROUP BY student.student_Name,Vak;

这就是我的回报。

结果 - imgbb.com [ ^ ]



这就是我想要的。



result2 - imgbb.com [ ^ ]

推荐答案

提示 - 显示与预期结果相比较的结果......

1.不包含图像链接 - 只需将数据作为预格式文本放入问题中

2.确保您的预期结果使用与实际结果相同的数据 - 您提供的图像中的数据项彼此无关



提供从每个表中查询的数据样本也是一个非常好的主意。帮助我们帮助你！



我很感兴趣你如何能够显示你的实际结果，因为当我运行你的查询时我得到一个错误 - 无效的列名字'Vak'。当我解决这个问题时，我开始收到类似Column'＃student.ID'在选择列表中无效的错误，因为它不包含在聚合函数或GROUP BY子句中。这是由于计算 Gemiddelde



我试图按如下方式重现您的数据：

Hint - when showing the results that you get compared to what you expected...
1. Do not include links to images - just put the data into your question as pre-formatted text
2. Make sure that your expected results use the same data as your actual results - the data items in the images you supplied are not related to each other

It's also an extremely good idea to provide samples of the data you are querying from each of the tables. Help us to help you!

I'm intrigued how you are able to show your actual results, as when I run your query I get an error - "Invalid column name 'Vak'". When I fix that I then start getting errors like "Column '#student.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". This is caused by the calculation for Gemiddelde

I've tried to reproduce your data as follows:

CREATE TABLE #student (ID int, student_Name VARCHAR(50))
insert into #student VALUES (1, 'Harmen'), (2, 'Remon'), (3, 'Stan'), (4, 'Frans de Boer'), (6, 'Kees')

CREATE TABLE #course (ID int, course_Name varchar(50))
insert into #course VALUES (1, 'Nederlands'), (2, 'Engels'), (3, 'Programmeren')

CREATE TABLE #test (ID int, Course_ID int, test_Name varchar(50))
insert into #test VALUES (1, 1, 'Dutch test 1'), (2, 2, 'English Test 1'), (3, 3, 'Programming Test 1')

CREATE TABLE #result (student_ID int, test_ID int, Result decimal(15,2))
insert into #result VALUES (1, 1, 7), (2, 1, 9), (2, 2, 10), (3, 1, 7.5), (3, 2, 8)

这个过程的第一步是获得所有结果以及每个学生的平均分数：

The first step in the process is to get all the results along with the average score per student:

SELECT (S.student_Name) AS Leerling, (C.course_Name) AS Vak, R.Result, AVG(R.Result) OVER (PARTITION BY R.student_ID)
FROM #course C
JOIN #test T ON C.ID = T.Course_ID
JOIN #result R ON T.ID = R.test_ID
JOIN #student S ON R.student_ID = S.ID

哪个给你

Harmen	Nederlands	7.00	7.000000
Remon	Nederlands	9.00	9.500000
Remon	Engels	       10.00	9.500000
Stan	Nederlands	7.50	7.750000
Stan	Engels	        8.00	7.750000

然后您可以按照本文中的说明进行操作在SQL查询中使用Pivot的简单方法 [ ^ ]以您想要的格式获取数据，例如

You can then follow the instructions on this article Simple Way To Use Pivot In SQL Query[^] to get the data in the format you want e.g.

SELECT Leerling, ISNULL(Nederlands,0) AS Nederlands, ISNULL(Engels,0) AS Engels, ISNULL(Programmeren,0) as Programmeren, Gemiddeld
FROM
(
	SELECT (S.student_Name) AS Leerling, (C.course_Name) AS Vak, R.Result, AVG(R.Result) OVER (PARTITION BY S.ID) AS Gemiddeld
	FROM #course C
	JOIN #test T ON C.ID = T.Course_ID
	JOIN #result R ON T.ID = R.test_ID
	JOIN #student S ON R.student_ID = S.ID
) as s
PIVOT 
(
	SUM(Result)
	FOR Vak IN (Nederlands, Engels, Programmeren)
) as p

结果：

Results:

Leerling  Nederlands  Engels Program.  Gemiddeld
Harmen		7.00	0.00	0.00	7.000000
Stan		7.50	8.00	0.00	7.750000
Remon		9.00   10.00	0.00	9.500000

其他注意事项

- 我使用了临时表，所以你需要删除＃符号

- 我已经使用了别名，因为我不想浪费时间输入完整的表名

- 通过使用窗口函数（AVG-OVER-PARTITION）我已经删除了对GROUP BY的需求（克服了错误）

Other things to note
- I've used temporary tables so you need to remove the # symbols
- I've used aliases because I don't want to waste time typing out the full table names
- By using a Window Function (AVG-OVER-PARTITION) I've removed the need for a GROUP BY (which overcomes the errors)

这篇关于如何显示表格行垂直的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！