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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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