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