如何在SQL查询中将列显示为行?
问题描述:
我试图显示包含两个表tblStudents
和tblExamResults
的简单数据库的检查结果。如何在SQL查询中将列显示为行?
tblstudents
包括学生证和FULL_NAME列
在tblexamResults
列student_id数据,主题和标志。在
下面图片
1-目前我显示学生成绩使用此查询
SELECT tblStudents.Full_Name, tblExamResults.Subject, tblExamResults.Marks
FROM tblExamResults INNER JOIN
tblStudents ON tblExamResults.Student_id = tblStudents.Student_ID
order by tblStudents.Full_Name
2 - 和结果看起来像下面的图片:
3 - 但我想是为了显示每个主题行和让每个学生的结果显示在同一行中获得它下面
每个科目的结果:
Student_Namesub1_resultsub2_Resultsub3_Result
如同下面的图片(Excel的屏幕截图)
所以:
- 我怎么能显示该格式数据?
- SQL Server中可能吗?
答
select fullname,[english] english, [history] history, [physics] physics
from
(
select fullname,subject,marks
from (yourquery)
) src
pivot
(
max(marks)
for subject in ([english], [history], [physics])
) piv;
或
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.subject)
FROM (yourquery) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT fullname, ' + @cols + ' from
(
select fullname,subject,marks
from (your query)
) x
pivot
(
max(marks)
for subject in (' + @cols + ')
) p '
execute(@query)
fullname english history physics
a 85 70 60
i 60 100 89
s 90 90 99
答
最后我用在FROM (yourquery) c
Declare @query nvarchar(max);
DECLARE @cols AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.subject)
FROM tblExamResults c ' parentheses Removed from (tblExamResults) c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
print(@cols)
set @query = '
SELECT *
FROM (
SELECT tblStudents.Full_Name, tblExamResults.Subject, tblExamResults.Marks
FROM tblExamResults INNER JOIN
tblStudents ON tblExamResults.Student_id = tblStudents.Student_ID
) as s
PIVOT
(
sum(marks) FOR subject IN ('+ @cols +')
)AS pvt'
;
execute(@query)
搜索'CROSS TAB','PIVOT'和'DYNAMIC SQL' – Squirrel
使用动态数据透视概念 – Mansoor