Dynamic procedure pivot not accept multi columns SQL server ?

Question

1.00/5 (1 vote)

See more:

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

Posted 13-Nov-21 9:25am

ahmed_sa

Updated 22-Nov-21 1:10am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2021-11-22T01:10:00

Look at your line of code

SQL

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

when you pass @STATIC_COLUMN as '[CompanyID]' @SQLSTRING will read

SQL

DECLARE @RETURN_TABLE ([CompanyID] NVARCHAR(255) NULL ...

That is valid syntax so the code will work.

BUT, now look what happens when you pass @STATIC_COLUMN as '[CompanyID],[Year]'. @SQLSTRING reads

SQL

DECLARE @RETURN_TABLE ([CompanyID], [Year] NVARCHAR(255) NULL ...

No type is defined for [CompanyID] and [Year] is defined as an NVARCHAR

There are ways around this - you could for example, query the system views for the column list so you know which type each column should be defined as in the declaration of @RETURN_TABLE. Personally I think that is making life far too over-complicated.

An alternative is to use a temporary table instead of a table variable, and avoid the need to declare/define the table before running the SQL that will populate it ... i.e replace

SQL

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'

with

SQL

SET @SQLSTRING = 'select * from (
                    SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' INTO #RETURN_TABLE FROM '+@TABLE+' ) a

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

                    SELECT * FROM #RETURN_TABLE'

Caveat: I have not tested this so I may have made minor syntactical errors, but hopefully this will give you enough of the principle, and you have data with which to test this, whereas I do not (it's always a good idea to share sample data and expected results)

Once last piece of advice, when creating dynamic SQL always PRINT the derived SQL string and try to run it in an IDE that will generate error messages - it's a lot easier to spot the problems that way

Dynamic procedure pivot not accept multi columns SQL server ?

1 solution

Solution 1

Add your solution here

Preview 0

Dynamic procedure pivot not accept multi columns SQL server ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us