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

_{Wednesday, August 12, 2020 6:04 AM}

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:20 AM ✅Answered | 1 vote}

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.