Dynamic procedure pivot not accept multi columns SQL server?

Transact-SQL
, ,
1

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
2

I dont think you have tried anything other than post the stored procedure
Who wrote this stored procedure? Did you?
Either way you need to show some effort in rewriting it which is really what you want someone to do it for you.

https://www.codeproject.com/Questions/5317551/Dynamic-procedure-pivot-not-accept-multi-columns-S

3

what i try as below
On line 47 replace @STATIC_COLUMN with

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

but error still exist

4

Instead of testing the stores procedure test the idea within the procedure.

What were you hoping to accomplish by the change you made?

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