如何在SQL Server 2005中创建动态行到列

将代码移动到存储过程

    /*
    GetExamResults 'MECH', '2'
*/
Create Proc GetExamResults (@Course_Id varchar(100), @Semester varchar(10))
as
begin
    declare @subjname varchar(100)  
    declare @subjects varchar(7000)  
    declare @subjectsselection varchar(7000)  
    declare @SumSelection varchar(7000)  
    declare @NoOfSubjects int
    set @NoOfSubjects = 0
    set @subjects = ''  
    set @subjectsselection = '' 
    set @SumSelection = ''
    DECLARE subject_cursor CURSOR  
    FOR SELECT distinct Subject_Name FROM Exam_Result where course_id = @Course_Id And Semester = @Semester 
    OPEN subject_cursor  
    FETCH NEXT FROM subject_cursor  
    INTO @subjname  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        set @subjects = @subjects + '[' + @subjname + '],'  
        set @subjectsselection = @subjectsselection + 'Sum(Isnull([' + @subjname + '],0)) As [' + @subjname + '],' 
        set @SumSelection = @SumSelection + 'Sum(Isnull([' + @subjname + '],0))+' 
        set @NoOfSubjects = @NoOfSubjects + 1
        FETCH NEXT FROM subject_cursor  
        INTO @subjname  
    End  
    CLOSE subject_cursor;  
    DEALLOCATE subject_cursor;  
    select @subjects = LEFT(@subjects, LEN(@subjects) - 1)  
    select @subjectsselection = LEFT(@subjectsselection, LEN(@subjectsselection) - 1)  
    select @SumSelection = LEFT(@SumSelection, LEN(@SumSelection) - 1)  
    print @subjects  
    print @subjectsselection  
    print @SumSelection
    declare @query nvarchar(4000)  
    set @query = 'select S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, ' + @subjectsselection + ',' 
    set @query = @query + 'Exam_Type,' + @SumSelection + ' As Grand_Total, '
    set @query = @query + '(' + @SumSelection + ')' + '/' + convert(varchar(10),@NoOfSubjects) + ' As Avg'
    set @query = @query + ' From '  
    set @query = @query + '(select Enroll_Number, Student_Name, Course_Id, Semester, Subject_Name, MarksObtained, Exam_Type from Exam_Result ) ps '  
    set @query = @query + ' pivot(sum(MarksObtained) for Subject_Name in (' + @subjects + ')) as pvt'  
    set @query = @query + ' inner join Stud_Info S on S.Enroll_Number = pvt.Enroll_Number '
    set @query = @query + ' where pvt.Course_Id = ''' + @Course_Id + ''' and pvt.Semester = ''' + @Semester + ''''
    set @query = @query + ' group by S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, Exam_Type'
    print @query
    exec sp_executesql @query  
end

你可以看看SQL Server中的PIVOT功能：http://msdn.microsoft.com/en-us/library/ms177410.aspx

但是，可能需要使用存储过程动态生成该语句。

我们可以使用 PIVOT 和动态 sql 语句来实现这一点。例如，我们有一个名为 sales 的表，如下所示。

create table Sales (SalesPerson varchar(100), Product varchar(50), SamesAmount numeric (18,2))
insert into Sales (SalesPerson, Product, SamesAmount)
select 'Bob',   'Pickles', 100.00
union all
select 'Sue',   'Oranges', 50.00
union all
select 'Bob',   'Pickles', 25.00
union all
select 'Bob',   'Oranges', 300.00
union all
select 'Sue',   'Oranges', 500.00

透视查询是

select salesperson, Isnull([Oranges],0) As Oranges, Isnull([Pickles],0) As Pickles
From
(select SalesPerson, Product, SamesAmount from Sales ) ps
pivot(sum(SamesAmount) for product in ([Oranges],[Pickles])) as pvt

由于主题的数量可以变化，我们必须构建动态 sql 查询。

declare @productname varchar(100)
declare @products varchar(7000)
declare @productsselection varchar(7000)
set @products = ''
set @productsselection = ''
DECLARE product_cursor CURSOR
    FOR SELECT distinct Product FROM Sales
OPEN product_cursor
FETCH NEXT FROM product_cursor
INTO @productname
WHILE @@FETCH_STATUS = 0
BEGIN
    set @products = @products + '[' + @productname + ']'
    set @productsselection = @productsselection + 'Isnull([' + @productname + '],0) As ' + @productname
    set @products = @products + ','
    set @productsselection = @productsselection + ','
    FETCH NEXT FROM product_cursor
    INTO @productname
End
CLOSE product_cursor;
DEALLOCATE product_cursor;
select @products = LEFT(@products, LEN(@products) - 1)
select @productsselection = LEFT(@productsselection, LEN(@productsselection) - 1)
print @products
print @productsselection
declare @query nvarchar(4000)
set @query = 'select salesperson, ' + @productsselection + ' From '
set @query = @query + '(select SalesPerson, Product, SamesAmount from Sales ) ps '
set @query = @query + 'pivot(sum(SamesAmount) for product in (' + @products + ')) as pvt'
exec sp_executesql @query