sql - 在 MS SQL 中设计数据透视表

Question

我正在尝试开发数据透视表但无法获得所需的结果。请参阅附图以获取所需的输出。我无法为所需的解决方案设计数据透视查询。

这是我的 sql 查询::

select 
    b.SchoolName,
    c.KaryakramName,
    a.NikashaAmount
from TBL_NIKASHA a
inner join TBL_SCHOOL b on b.SchoolId = a.SchoolId
inner join TBL_KARYAKRAM c on a.KaryakramId = c.KaryakramId
WHERE 
a.NikashaType = 1 and c.karyakramType = 1
order by a.SchoolId;

在这里，NikashaType 和 karyakramType 来自用户。

我必须在数据透视表中显示此查询的结果。到目前为止我所做的::

declare
    @columns nvarchar(max) = '',
    @sql nvarchar(max) = '';
    
select
    @columns += QUOTENAME(c.KaryakramName) + ','
from TBL_NIKASHA a
inner join TBL_SCHOOL b on b.SchoolId = a.SchoolId
inner join TBL_KARYAKRAM c on a.KaryakramId = c.KaryakramId
WHERE 
a.NikashaType = 1 and c.karyakramType = 1

SET @columns = left(@columns, len(@columns)-1);

set @sql = '
    select * from 
(
        select c.KaryakramName
        from TBL_NIKASHA a
        inner join TBL_SCHOOL b on b.SchoolId = a.SchoolId
        inner join TBL_KARYAKRAM c on a.KaryakramId = c.KaryakramId
        WHERE 
        a.NikashaType = 1 and c.karyakramType = 1
        order by a.SchoolId
) t
pivot
(
    a.NikashaAmount,
    for c.KaryakramName in ('+ @columns+')
) as pivot_table
';


execute sp_execute @sql;

我收到错误消息：过程需要'int'类型的参数'@handle'。

标签： sqlasp.netsql-serverpivot