Dynamic procedure pivot not accept multi columns SQL server ?

asked 2021-11-13T21:04:06.817+00:00
ahmed salah 3,126 Reputation points

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,473 questions
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2021-11-13T22:20:24.823+00:00
    Erland Sommarskog 67,636 Reputation points Microsoft MVP

    On line 47 replace @STATIC_COLUMN with

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

  2. answered 2021-11-14T11:17:09.763+00:00
    Erland Sommarskog 67,636 Reputation points Microsoft MVP

    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'
    
    No comments