SQL输出数据透视表

CREATE PROCEDURE [dbo].[p_Pivot_WithSum_VH](@tabName nvarchar(MAX),@rowName nvarchar(MAX),@colName nvarchar(MAX),
    @numName nvarchar(MAX),@calcMethod nvarchar(MAX))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    -- Compared with the sp [p_Pivot] or [p_Pivot_WithSum], this sp adds the subtotal line for both rows and columns.
    -- Especially, this sp processes the name shown in the returned table as [*] if it's a string field, and [blank] if time/number/etc.


    DECLARE @sql nvarchar(MAX),@col nvarchar(MAX),@sql_col nvarchar(MAX)

    SELECT @sql_col=N'SELECT @col_out=ISNULL(@col_out+'','','''')+QUOTENAME(['+@colName+']) FROM ['+@tabName
        +'] GROUP BY ['+@colName+'] ORDER BY ['+@colName+']'
    EXEC SP_EXECUTESQL @sql_col,N'@col_out nvarchar(MAX) OUTPUT',@col_out=@col OUTPUT


    declare @rowList nvarchar(max),@row_first nvarchar(100),@row_last nvarchar(100),@rowRank nvarchar(max)
    if @rowName like '%,%'
    begin
        select @row_first=left(@rowName,charindex(',',@rowName)-1)
        select @row_last=right(@rowName,charindex(',',reverse(@rowName))-1)
    end
    else
        select @row_first=@rowName,@row_last=@rowName

    select @rowList=isnull(@rowList+' and ','')+'t1.'+ElementOutput+'=t2.'+ElementOutput from dbo.StringToList(@rowName,',')
    select @rowRank=isnull(@rowRank+',','')+'t1.'+ElementOutput from dbo.StringToList(@rowName,',')


    declare @rep_value table(Field nvarchar(max),FType nvarchar(100),FTypeId int)
    declare @ttl nvarchar(max)=','+@rowName+',',@sInput nvarchar(100),@sOutput nvarchar(100)

    if @tabName like '%#%'
    begin 
    insert into @rep_value
    select t2.ElementOutput,case when xtype in (167,175,231,239) then '''*''' else 'null' end TotalInfo,xtype from (
    select * from tempdb..syscolumns where id=OBJECT_ID('tempdb..'+@tabName)) t1 full join dbo.StringToList(@rowName,',') t2 on t1.name=t2.ElementOutput
    end
    else
    begin
    insert into @rep_value
    select t2.ElementOutput,case when xtype in (167,175,231,239) then '''*''' else 'null' end TotalInfo,xtype from (
    select * from syscolumns where id=OBJECT_ID(@tabName)) t1 full join dbo.StringToList(@rowName,',') t2 on t1.name=t2.ElementOutput
    end

    while exists(select * from @rep_value where @ttl like '%,'+Field+',%')
    begin
        select @sInput=','+Field+',',@sOutput=','+FType+',' from @rep_value where @ttl like '%,'+Field+',%'
        select @ttl=replace(@ttl,@sInput,@sOutput)
    end

    select @ttl=SUBSTRING(@ttl,2,len(@ttl)-2)

    IF @rowName IS NULL
        SELECT @sql=N'SELECT '''+@numName+''' AS ['+@colName+'],* FROM (SELECT ['+@colName+'],['+@numName+'] FROM ['+@tabName+']) t 
        PIVOT('+@calcMethod+'(['+@numName+']) FOR ['+@colName+'] IN ('+@col+')) p'
    ELSE
        SELECT @sql=N'SELECT t1.*,t2.[*] FROM (SELECT *,0 AS SubtotalRow FROM (
        
        SELECT '+@rowName+',['+@colName+'],['+@numName+'] FROM ['+@tabName+']) t 
            PIVOT('+@calcMethod+'(['+@numName+']) FOR ['+@colName+'] IN ('+@col+')) p 
        UNION 
        SELECT '+@ttl+',*,1 FROM (SELECT '+@colName+','+@calcMethod+'('+@numName+') AS I FROM '+@tabName+' GROUP BY '+@colName+
        ') t PIVOT(SUM([I]) FOR ['+@colName+'] IN ('+@col+')) p
        
        ) t1  left join (
        select grouping(['+@row_first+']) SubtotalRow,'+@rowName+',['+@colName+'],'+@calcMethod+'(['+@numName+']) [*] from ['+@tabName+'] group by '+@rowName+',['+@colName+'] with rollup having grouping(['+@colName+'])=1 and (['+@row_first+'] is null or ['+@row_last+'] is not null)
        ) t2 on '+@rowList+' or t1.SubtotalRow=1 and t2.SubtotalRow=1
        
         ORDER BY t1.SubtotalRow,'+@rowRank
    PRINT @sql
    EXEC SP_EXECUTESQL @sql


END