locked
pivot table error identifier is too long. Maximum length is 128. RRS feed

  • Question

  • User1126057398 posted

    I am creating a dynamic query like below to compare activities inputted by User. It's working fine when Inputted Activity length is small but if Activity is too long like 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210'
    It gives error 'The identifier that starts with 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort ' is too long. Maximum length is 128.' 


    DECLARE @ActivityToCompare nvarchar(MAX) --='Inactivity Light Meditating','Self Care Taking Medication, Sitting Or Standing'
    --Square brackets are added since 1st & 2nd Activity sometimes contain bracket[eg. Chapati (Big)]
    SET @ActivityToCompare= CONCAT('[' + @FirstActivity,'],[', @SecondActivity + ']')
    DECLARE @Sql varchar(max)

    SET @Sql = ';WITH x AS
    (
    SELECT LTRIM(RTRIM(MET_ACTIVITY_DESCRIPTION)) AS MET_ACTIVITY_DESCRIPTION , num, sn
    FROM (
    SELECT MET_ACTIVITY_DESCRIPTION,
    [Exercise Category] = CONVERT(varchar(32), Category),
    [MET VALUE] = CONVERT(varchar(32), MET_VALUE),
    [Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')
    FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
    ) AS d
    UNPIVOT (num FOR sn IN
    ([Exercise Category], [MET VALUE], [Calories Burn/Hour])
    ) AS unp
    )
    SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToCompare + ' FROM x
    PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;'

    PRINT(@Sql)
    EXEC(@Sql)

    Friday, December 6, 2019 9:01 AM

Answers

  • User1126057398 posted

    Rather then comparing by Text I had Compared it by Code and latter in pivot query used 'as' ie alias name to display as per requirement.

    DECLARE @ActivityToCompare nvarchar(MAX) --='Inactivity Light Meditating','Self Care Taking Medication, Sitting Or Standing'
    --Square brackets are added since 1st & 2nd Activity sometimes contain bracket[eg. Chapati (Big)]
    --SET @ActivityToComp= CONCAT('[' + @FirstActivity,'],[', @SecondActivity + ']') --Commented since now comparing by 'Code' rather then 'MET_ACTIVITY_DESCRIPTION'
    SET @ActivityToCompare= CONCAT('[' + @FirstActivityCode,'],[', @SecondActivityCode + ']')
    SET @ActivityToComp = CONCAT('[' + @FirstActivityCode, + '] as [' + @FirstActivity + '],[' + @SecondActivityCode + '] as [' + @SecondActivity +']')
    DECLARE @Sql varchar(max)

    SET @Sql = ';WITH x AS
    (
    SELECT LTRIM(RTRIM(CODE)) AS MET_ACTIVITY_DESCRIPTION , num, sn
    FROM (
    SELECT A.Code,
    [Exercise Category] = CONVERT(varchar(32), Category),
    [MET VALUE] = CONVERT(varchar(32), MET_VALUE),
    [Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')
    FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
    ) AS d
    UNPIVOT (num FOR sn IN
    ([Exercise Category], [MET VALUE], [Calories Burn/Hour])
    ) AS unp
    )
    SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToComp + ' INTO #TempTableTesting FROM x
    PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;
    Select * FROM #TempTableTesting '

    --PRINT(@Sql)
    EXEC(@Sql)

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 9, 2019 5:32 AM

All replies

  • User1126057398 posted

    Rather then comparing by Text I had Compared it by Code and latter in pivot query used 'as' ie alias name to display as per requirement.

    DECLARE @ActivityToCompare nvarchar(MAX) --='Inactivity Light Meditating','Self Care Taking Medication, Sitting Or Standing'
    --Square brackets are added since 1st & 2nd Activity sometimes contain bracket[eg. Chapati (Big)]
    --SET @ActivityToComp= CONCAT('[' + @FirstActivity,'],[', @SecondActivity + ']') --Commented since now comparing by 'Code' rather then 'MET_ACTIVITY_DESCRIPTION'
    SET @ActivityToCompare= CONCAT('[' + @FirstActivityCode,'],[', @SecondActivityCode + ']')
    SET @ActivityToComp = CONCAT('[' + @FirstActivityCode, + '] as [' + @FirstActivity + '],[' + @SecondActivityCode + '] as [' + @SecondActivity +']')
    DECLARE @Sql varchar(max)

    SET @Sql = ';WITH x AS
    (
    SELECT LTRIM(RTRIM(CODE)) AS MET_ACTIVITY_DESCRIPTION , num, sn
    FROM (
    SELECT A.Code,
    [Exercise Category] = CONVERT(varchar(32), Category),
    [MET VALUE] = CONVERT(varchar(32), MET_VALUE),
    [Calories Burn/Hour] = CONVERT(varchar(32), MET_VALUE*'+ convert(varchar,@weight) + ')
    FROM DIET_MET_DETAIL_MST A LEFT OUTER JOIN DIET_MET_CAT_MST B on A.Category_Code=B.Code
    ) AS d
    UNPIVOT (num FOR sn IN
    ([Exercise Category], [MET VALUE], [Calories Burn/Hour])
    ) AS unp
    )
    SELECT MET_ACTIVITY_DESCRIPTION = sn,' + @ActivityToComp + ' INTO #TempTableTesting FROM x
    PIVOT (MAX(num) FOR MET_ACTIVITY_DESCRIPTION IN (' + @ActivityToCompare + ')) AS p order by MET_ACTIVITY_DESCRIPTION;
    Select * FROM #TempTableTesting '

    --PRINT(@Sql)
    EXEC(@Sql)

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 9, 2019 5:32 AM
  • User665608656 posted

    Hi geetasks,

    This error is usually that you use double quotation marks in statements.

    In general, I suggest that you can replace the double quotation marks in statements with single quotation marks.

    You can check through the SQL statements printed out, such as If you still can't solve your issue, I suggest you give us your data table structure and the print SQL statement for reference.

    You can also refer to this link:

    The identifier that starts with … is too long. Maximum length is 128

    Best Regards,

    YongQing.

    Monday, December 9, 2019 7:21 AM
  • User1126057398 posted

    Thanks Yong. But the issue is  that column name length=145 in Select query. I am converting column value as column name using pivot. Everything is working fine but if column name Length>128 it throws an error, eg take the below Select Query, it throws error since alias name(ie Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210) length>128

    Select MET_ACTIVITY_DESCRIPTION,2101 as 'Conditioning Exercise Weight Lifting (Free Weight, Nautilus Or Universal-Type), Power Lifting Or Body Building, Vigorous Effort (Taylor Code 210',13036 as 'Self Care Taking Medication, Sitting Or Standing' FROM #TempTableTesting

    I had updated Column Value so that Length<120 

    Monday, December 9, 2019 12:47 PM