sqlserver存储过程一些学习技巧

begin
    if (object_id('tempdb..#Test') is not null) drop table #Test ;
    create table #Test ([id]      [int] identity (1, 1)                    not null,
                        [name]    [nvarchar](50) collate Chinese_PRC_CI_AS null,
                        [subject] [nvarchar](50) collate Chinese_PRC_CI_AS null,
                        [Source]  [numeric](18, 0)                         null) on [PRIMARY]
    insert into #Test ([name], [subject], [Source])
    values (N'张三', N'ch', 60)
    insert into #Test ([name], [subject], [Source])
    values (N'李四', N'ma', 70)
    insert into #Test ([name], [subject], [Source])
    values (N'王五', N'eg', 80)
    insert into #Test ([name], [subject], [Source])
    values (N'王五', N'ma', 75)
    insert into #Test ([name], [subject], [Source])
    values (N'王五', N'ch', 57)
    insert into #Test ([name], [subject], [Source])
    values (N'李四', N'ch', 80)
    insert into #Test ([name], [subject], [Source])
    values (N'张三', N'eg', 100)

    declare @tpl nvarchar(max) = 'sum(case when x0.subject = ''_subject_'' then Source end) as _subject_,';
    declare @exp nvarchar(max) = (select replace(@tpl, '_subject_', x0.subject) from #Test x0 for xml path (''));
    set @exp = substring(@exp, 1, len(@exp) - 1);
    declare @sql nvarchar(max) =' select x0.name, _exp_ from #Test x0 group by x0.name;'
    set @sql = replace(@sql, '_exp_', @exp);
    exec (@sql);
end;