Dynamic procedure pivot not accept multi columns SQL server ?

  • 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
  • Thanks for posting your issue and hopefully someone will answer soon.

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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

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

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