Click here to Skip to main content
15,172,351 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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
Updated 22-Nov-21 2:10am

1 solution

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
   

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900