如何在sql Server 2008中设置查询 [英] How to Set Query in sql Server 2008

问题描述

1）ExamMast

1)ExamMast

ExamID	ExamName   Mark	
1	Maths	    50	
2	SS	    50	
3	Hindi	    50

2）ExamTransaction

2)ExamTransaction

ExamTranId	ExamID	OutMark	StudentId
1	1	25	1
2	1	30	2
3	2	24	1
4	2	26	2
5	3	30	1
6	3	48	2

3）如何获得此TYPE输出。

3)How to Get This TYpe Output.

Name	Maths	SS	Hindi
1	25	24	30
2	30	26	48

我有Craet 2表ExamMast和ExamTransaction。

但是使用查询最终输出。（3表）。

和考试类型最多N ...类型..

全部转换为列。和学生标记逐一设置学生ID ...如同3无表。

I have Craet 2 table ExamMast and ExamTransaction.
But Using Query Final Output.(3 Table).
And Exam type up to N... type..
All convert to column. and student mark set one by one student Id...like as 3 No table.

推荐答案

试试这个：

Try this:

CREATE TABLE #ExamMast (ExamID INT IDENTITY(1,1), ExamName VARCHAR(30), Mark INT)
INSERT INTO #ExamMast (ExamName, Mark)
SELECT 'Maths', 50	
UNION ALL SELECT 'SS', 50	
UNION ALL SELECT 'Hindi', 50

CREATE TABLE #ExamTransaction (ExamTranId INT IDENTITY(1,1), ExamID INT, OutMark INT, StudentId INT)
INSERT INTO #ExamTransaction (ExamID, OutMark, StudentId)
SELECT 1, 25, 1
UNION ALL SELECT 1, 30, 2
UNION ALL SELECT 2, 24, 1
UNION ALL SELECT 2, 26, 2
UNION ALL SELECT 3, 30, 1
UNION ALL SELECT 3, 48, 2


DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + ExamName
					FROM #ExamMast
					ORDER BY '],[' + ExamName
			FOR XML PATH('')),1,2,'') + ']'

--SELECT @cols AS [cols]

SET @dt = N'SELECT et.StudentId, em.ExamName, et.OutMark ' +
			'FROM #ExamMast AS em INNER JOIN #ExamTransaction AS et ON em.ExamId = et.ExamId'
--EXEC(@dt)

SET @pt = N'SELECT StudentId AS [Name], ' + @cols + ' ' +
			'FROM (' + @dt + ') AS DT ' +
			'PIVOT(MAX(OutMark) FOR ExamName IN (' + @cols + ')) AS PT'
EXEC(@pt)

DROP TABLE #ExamMast
DROP TABLE #ExamTransaction

这篇关于如何在sql Server 2008中设置查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！