要获得问题结果的查询将是:

create table StudentResults(StudentID int,Name nvarchar(50),Course nvarchar(50), CourseLevel nvarchar(10));
insert into StudentResults values(1,'John','English','E2'),(1,'John','Maths','E3'),(1,'John','Computing','L2');

select StudentID
        ,Name
        ,[Computing]
        ,[Maths]
        ,[English]
from StudentResults
pivot(max(CourseLevel) for Course in([Computing],[Maths],[English])
     ) as p;

输出:

StudentID   Name    Computing   Maths   English
1           John    L2          E3      E2

尽管您可能会得出结论，但这需要对主题进行硬编码.如果您的主题列表可能会更改，则此查询将不再适合该目的.

如果您感到舒适，则可以使用动态SQL对此进行补救:

declare @cols as  nvarchar(max)
       ,@query as nvarchar(max);

set @cols = stuff(
                   (select distinct ','+quotename(Course)
                    from StudentResults
                    for xml path(''),type).value('.','nvarchar(max)'
                   )
                 ,1,1,''
                 );

set @query = 'select StudentID
                    ,Name
                    ,'+@cols+'
            from StudentResults
            pivot (max(CourseLevel) for Course in ('+@cols+')
                  ) p';

execute (@query);

但是理想情况下，您只需要返回一组数据，就好像它出现在源表中一样，并让您的报告层(例如s-s-rS)处理数据透视，它比纯SQL更适合于数据透视. /p>

I'm having trouble with a Cross Tab query in SQL Server and was hoping that someone could please help?

I have the following table:

- Student ID - Name - Course     - Course Level -
- 1          - John - English    - E2 -
- 1          - John - Mathns     - E3 -
- 1          - John - Computing  - L2 -

Each learner has to sit an English, Maths and Computing Assessment and a level is given.

I need to report what one learner achieved in each assessment on one line like the following:

- StudentID - Name - English - Maths - Computing - 
- 1         - John - E2      - E3    - L2 -

I have been given this code from the comments below: Thank you @iamdave.

SELECT PERSON_CODE, [Computing], [Maths], [English]
FROM TT
PIVOT (MAX(LEVEL) FOR COURSE_CODE IN ([DL], [NUM15], [ENG15])) AS P

I just need to add a group by so that John only appears as one row as I am currently seeing this:

- StudentID - Name - English - Maths - Computing - 
- 1         - John - E2      - E3    - L2 -
- 1         - John - E2      - E3    - L2 -
- 1         - John - E2      - E3    - L2 -

Instead of:

- StudentID - Name - English - Maths - Computing - 
- 1         - John - E2      - E3    - L2 -
- 2         - Amy  - L1      - L2    - E3 -

Thank you

The query you will need to get the results in your question is:

create table StudentResults(StudentID int,Name nvarchar(50),Course nvarchar(50), CourseLevel nvarchar(10));
insert into StudentResults values(1,'John','English','E2'),(1,'John','Maths','E3'),(1,'John','Computing','L2');

select StudentID
        ,Name
        ,[Computing]
        ,[Maths]
        ,[English]
from StudentResults
pivot(max(CourseLevel) for Course in([Computing],[Maths],[English])
     ) as p;

Output:

StudentID   Name    Computing   Maths   English
1           John    L2          E3      E2

Though as you may be able to work out, this requires hard coding the subjects. If your list of subjects is likely to change, then this query will no longer be fit for purpose.

If you are comfortable, you can remedy this with dynamic SQL:

declare @cols as  nvarchar(max)
       ,@query as nvarchar(max);

set @cols = stuff(
                   (select distinct ','+quotename(Course)
                    from StudentResults
                    for xml path(''),type).value('.','nvarchar(max)'
                   )
                 ,1,1,''
                 );

set @query = 'select StudentID
                    ,Name
                    ,'+@cols+'
            from StudentResults
            pivot (max(CourseLevel) for Course in ('+@cols+')
                  ) p';

execute (@query);

Ideally though, you would simply return a set of data, as it appears to be in your source table and let your reporting layer (s-s-rS for example) handle the pivoting, which it is much better suited towards than pure SQL.