无法制定查询以使用数据透视表在单行中合并不同的行值 [英] Not able to formulate query to combine different row values in single row using pivot 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:

上面只有3个主题，但也可以超过3个主题.

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

要获得主题列表，我在下面使用相同的查询，并获得如下主题: [vb]，[c(p)]，VB(p)存储在我试图在数据透视表中使用的单个变量中.

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.

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)

完整过程如下:

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

但是我做不到.请帮助

下面提供给我实际表，我需要对结果表进行操作

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
                          )

我使用了[vb]，[vb(p)]，[C(P)]而不是@values(它包含主题列表)，因为在下面的部分中使用@value会给我带来错误:

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

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

以下是数据:

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

推荐答案

这是您的查询:

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)

这篇关于无法制定查询以使用数据透视表在单行中合并不同的行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！