locked
Conversion failed when converting the varchar value '],[' to data type int. RRS feed

  • Question

  • Below error i am getting 

    Msg 245, Level 16, State 1, Line 3
    Conversion failed when converting the varchar value '],[' to data type int.

    query

    declare @columns varchar(max)
    declare @convert varchar(max)
    select   @columns = stuff (( select distinct'],[' +  OrderNo
                        from Probale
                      
                        for xml path('')), 1, 2, '') + ']'
    
    
    set @convert =
    'select * from (select orderno,codeitem,prdqty from Probale) smallbale
        pivot(sum(prdqty) for Orderno
        in ('+@columns+')) as pivottable'
    
    
    execute (@convert)


    akhter

    Wednesday, August 12, 2020 6:04 AM

Answers

  • Hi Akhterhussain,

    Please try:

    declare @columns varchar(max)
    declare @convert varchar(max)
    select   @columns = stuff (( select distinct'],[' +  CAST(OrderNo AS VARCHAR(255))
                        from Probale
                      
                        for xml path('')), 1, 2, '') + ']'
    
    
    set @convert =
    'select * from (select orderno,codeitem,prdqty from Probale) smallbale
        pivot(sum(prdqty) for Orderno
        in ('+@columns+')) as pivottable'
    
    
    execute (@convert)

    Echo


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    • Edited by Echo Liuz Wednesday, August 12, 2020 6:20 AM
    • Marked as answer by Akhterhussain Wednesday, August 12, 2020 6:34 AM
    Wednesday, August 12, 2020 6:20 AM