Dynamic procedure pivot not accept multi columns SQL server ?

Question

I work on SQL server 2012 i face issue when using multi column ON COLUMN as companyid,Year

exec [dbo].[USP_DYNAMIC_PIVOT] '[CompanyID],[Year]','MetarialID','Metarialperc','#KTempSemlterfinialRows','max'

it give me error as

Msg 173, Level 15, State 13, Line 1
The definition for column 'CompanyID' must include a data type.

if i use only one columns as companyid it working pivot without any issue

exec [dbo].[USP_DYNAMIC_PIVOT] '[CompanyID]','MetarialID','Metarialperc','#KTempSemlterfinialRows','max'

so how to solve issue of use multi column on procedure [dbo].[USP_DYNAMIC_PIVOT]

What I have tried:

SQL

create PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
    (
        @STATIC_COLUMN VARCHAR(255),
        @PIVOT_COLUMN VARCHAR(255),
        @VALUE_COLUMN VARCHAR(255),
        @TABLE VARCHAR(255),
        @AGGREGATE VARCHAR(20) = null
    )

AS


BEGIN

SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
        @SQLSTRING NVARCHAR(MAX),
        @PIVOT_SQL_STRING NVARCHAR(MAX),
        @TEMPVARCOLUMNS NVARCHAR(MAX),
        @TABLESQL NVARCHAR(MAX)

if isnull(@AGGREGATE,'') = '' 
    begin
        SET @AGGREGATE = 'MAX'
    end


 SET @PIVOT_SQL_STRING =    'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                            FROM '+@TABLE+'
                            WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
                            FOR XML PATH(''''), TYPE)
                            .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
                            from '+@TABLE+' ma
                            ORDER BY ' + @PIVOT_COLUMN + ''

declare @TAB AS TABLE(COL NVARCHAR(MAX) )

INSERT INTO @TAB EXEC SP_EXECUTESQL  @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT 

SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)


SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')


SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')  
                    INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')

                    select * from (
                    SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a

                    PIVOT
                    (
                    '+@AGGREGATE+'('+@VALUE_COLUMN+')
                    FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
                    ) piv

                    SELECT * FROM @RETURN_TABLE'



EXEC SP_EXECUTESQL @SQLSTRING

END

Answer 1

On line 47 replace @STATIC_COLUMN with

replace(@STATIC_COLUMN, ',', 'nvarchar(255), ')

Answer 2

issue still exist after add replace on line 47
when add multi column it give me error

You did actually run the create procedure statement? And changed CREATE PROCEDURE to ALTER PROCEDURE, didn't you?

It worked when I tested it. I did not have your tables, so I just took a random system table, but that should not really matter. Anyway, below is my full procedure with test case and also two debug PRINT.

ALTER PROCEDURE [dbo].[USP_DYNAMIC_PIVOT]
    (
        @STATIC_COLUMN VARCHAR(255),
        @PIVOT_COLUMN VARCHAR(255),
        @VALUE_COLUMN VARCHAR(255),
        @TABLE VARCHAR(255),
        @AGGREGATE VARCHAR(20) = null
    )
AS

BEGIN

   SET NOCOUNT ON;
   declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
           @SQLSTRING NVARCHAR(MAX),
           @PIVOT_SQL_STRING NVARCHAR(MAX),
           @TEMPVARCOLUMNS NVARCHAR(MAX),
           @TABLESQL NVARCHAR(MAX)

   if isnull(@AGGREGATE,'') = '' 
       begin
           SET @AGGREGATE = 'MAX'
       end


    SET @PIVOT_SQL_STRING =    'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                               FROM '+@TABLE+'
                               WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
                               FOR XML PATH(''''), TYPE)
                               .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
                               from '+@TABLE+' ma
                               ORDER BY ' + @PIVOT_COLUMN + ''

   declare @TAB AS TABLE(COL NVARCHAR(MAX) )

   INSERT INTO @TAB EXEC sp_executesql  @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT 
PRINT @PIVOT_SQL_STRING        
   SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)

   SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')


   SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+ replace(@STATIC_COLUMN, ',', 'nvarchar(255), ') +' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')  
                       INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')

                       select * from (
                       SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+' ) a

                       PIVOT
                       (
                       '+@AGGREGATE+'('+@VALUE_COLUMN+')
                       FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
                       ) piv

                       SELECT * FROM @RETURN_TABLE'
PRINT @SQLSTRING
     EXEC sp_executesql @SQLSTRING

END
go
exec [dbo].[USP_DYNAMIC_PIVOT] '[object_id] ,[column_id]','system_type_id','max_length','sys.columns','max'