Dynamic procedure pivot not accept multi columns SQL server ?

Question

Post reply

Dynamic procedure pivot not accept multi columns SQL server ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

November 13, 2021 at 10:06 pm

#3952777

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:

 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

Personally, I avoid the PIVOT operator like the plague. I prefer CROSSTABs for functionality, performance, and more.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 3

how to handle features or columns that i don't know it

suppose i add new feature today and every feature have column

then every feature added will modify on code

if you are another way dynamic without change on code

please tell me

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

ahmed_elbarbary.2010 wrote:

how to handle features or columns that i don't know it
suppose i add new feature today and every feature have column
then every feature added will modify on code
if you are another way dynamic without change on code
please tell me

If you are hoping for a coded solution to your problem, you will need to provide consumable (ready to paste into SSMS) DDL, sample data and desired results based on that sample data. Otherwise, the best you can hope for is that people will provide links to dynamic pivot/crosstab examples.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin