无法使用数据透视表制定查询以在单行中组合不同的行值

Below is the actual table

下面是实际的表格

In the table above:
1)  FEID is the examination ID which remains same for one exam, like ist semester examination of particular class. So it will remain same for all rows in above table as it consists of data of single exam always.
2)  To store result of single student, marks for each subject are entered in each row. So if there are 5 subjects in a class,For each student marks of 5 subjects will be stored in 5 separate rows with marks obtained in each subject
3)  Result, Result_code, NCHMCTID remain same in each row of single student. Like in above table, their values remain same in 3 rows.
Due to some reasons I cant remove this redundancy

So my question is, I need to store result of one student in single row, but number of rows related to single student to store each subject marks is not pre determined(number of subjects can change and determined dynamically)
So , if I have 5 subjects marks in 5 rows, I need those in single row. 

Below is exactly what I need to convert above table into:

以下是我需要将上表转换为:

Above there are only 3 subjects, but they can be more than 3 subjects also.

上面只有3个科目,但也可以超过3个科目。

To get subjects list, I use below query for the same and get subjects like: [vb],[c(p)],VB(p) stored in single variable which I was trying to use in pivot table.

为了得到主题列表,我使用下面的查询相同,并得到像[vb],[c(p)],VB(p)存储在我试图在数据透视表中使用的单个变量的主题。

DECLARE @values varchar(max);
SET @values = '';
SELECT @values = @values +'['+ CAST(SubjectName AS varchar(max))+ ']' + ','
FROM tbSubjects where SubID IN(Select SubID from tbFinalMarks Where FEID=2) ;
SET @values = SUBSTRING(@values, 1, Len(@values) - 1)

Full procedure is below :

完整程序如下:

ALTER PROCEDURE    [dbo].[prFinalMarksLoadByFEID]

@FEID int 
AS
BEGIN


SET NOCOUNT ON;
DECLARE @values varchar(max);
SET @values = '';
SELECT @values = @values +'['+ CAST(SubjectName AS varchar(max))+ ']' + ','
FROM tbSubjects where SubID IN(Select SubID from tbFinalMarks Where FEID=2) ;
SET @values = SUBSTRING(@values, 1, Len(@values) - 1)
SELECT @values As 'Values'


 select Student_Name,@values,Result,NCHMCTID,Examination_Name from 

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

(SELECT     dbo.tbStudent.Name AS Student_Name, dbo.tbSubjects.SubjectName AS Subject_Name, dbo.tbFinalMarks.MarksObtained AS Marks_Obtained, 
                      dbo.tbFinalMarks.Result, dbo.tbFinalMarks.ResultCode AS Result_Code, ISNULL(dbo.tbStudent.NCHMCTID, 'Not Available') AS NCHMCTID, 
                      dbo.tbFinalExam.ExaminationName as Examination_Name
FROM         dbo.tbFinalMarks INNER JOIN
                      dbo.tbSubjects ON dbo.tbFinalMarks.SubID = dbo.tbSubjects.SubID INNER JOIN
                      dbo.tbStudent ON dbo.tbFinalMarks.StdID = dbo.tbStudent.StudentID INNER JOIN
                      dbo.tbFinalExam ON dbo.tbFinalMarks.FEID = dbo.tbFinalExam.FEID  
Where FEID =@FEID
                      ) ps
                      PIVOT
                      (
                        MAX(Marks_Obtained) For Subject_Name IN ([VB],[VB(P)],[C(P)])
                      ) AS pvt

But I am not able to do it. Please help

但我无法做到。请帮忙

Below part give me actual table which i need to manipulate for result table

下面的部分给出了我需要操作结果表的实际表

(SELECT     dbo.tbStudent.Name AS Student_Name, dbo.tbSubjects.SubjectName AS Subject_Name, dbo.tbFinalMarks.MarksObtained AS Marks_Obtained, 
                          dbo.tbFinalMarks.Result, dbo.tbFinalMarks.ResultCode AS Result_Code, ISNULL(dbo.tbStudent.NCHMCTID, 'Not Available') AS NCHMCTID, 
                          dbo.tbFinalExam.ExaminationName as Examination_Name
    FROM         dbo.tbFinalMarks INNER JOIN
                          dbo.tbSubjects ON dbo.tbFinalMarks.SubID = dbo.tbSubjects.SubID INNER JOIN
                          dbo.tbStudent ON dbo.tbFinalMarks.StdID = dbo.tbStudent.StudentID INNER JOIN
                          dbo.tbFinalExam ON dbo.tbFinalMarks.FEID = dbo.tbFinalExam.FEID  
    Where FEID =@FEID
                          )

I used [vb],[vb(p)],[C(P)] instead of @values ( it contains subjects list) as using @ values in below part gives me error:

我使用[vb],[vb(p)],[C(P)]而不是@values(它包含主题列表),因为使用下面部分的@值给出了错误:

 PIVOT
                      (
                        MAX(Marks_Obtained) For Subject_Name IN ([VB],[VB(P)],[C(P)])
                      ) AS pvt

Below is the data:

以下是数据:

FEID Student_Name Subject_Name Marks_Obtained Result Result_Code NCID Exam_Name     
  2 roof       VB           100       First     1234   ist semester
  2 roof       VB(P)    100       First     1234   ist semester
  2 roof       C(P)     100       First     1234   ist semester
  2 Amir       VB       100       First     nbb 8   ist semester
  2 Amir       VB(P)    100       First     nbb 8   ist semester
  2 Amir       C(P)     100       First     nbb 8   ist semester

1 个解决方案

#1

Here's your query:

这是您的查询:

create table #t (FEID int, Student_Name char(4), Subject_Name char(5), Marks_Obtained int, 
Result char(5), Result_Code int, NCID char(5), Exam_Name char(12))
go
insert #t values
(  2, 'roof',       'VB   ',   100,       'First',  NULL,   '1234 ',   'ist semester'),
(  2, 'roof',       'VB(P)',   100,       'First',  NULL,   '1234 ',   'ist semester'),
(  2, 'roof',       'C(P) ',   100,       'First',  NULL,   '1234 ',   'ist semester'),
(  2, 'Amir',       'VB   ',   100,       'First',  NULL,   'nbb 8',   'ist semester'),
(  2, 'Amir',       'VB(P)',   100,       'First',  NULL,   'nbb 8',   'ist semester'),
(  2, 'Amir',       'C(P) ',   100,       'First',  NULL,   'nbb 8',   'ist semester')
go

declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(subject_name) 
            FROM #t -- your table here
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
select @collist


declare @q nvarchar(max)
set @q = '
select * 
from (
    select 
    Student_Name, subject_name, Marks_Obtained, Exam_Name, Result, NCID, Result_Code
        from (
        select *
        from #t -- your table here
    ) as x
) as source
pivot (
    max(Marks_Obtained)
    for subject_name in (' + @collist + ')
) as pvt
'

exec (@q)