I have a query that returns a row of multiple Itemcodes
. The result is
Date group list of item code 2015-04-15 118 FYCT-00063,FYCM-00016,FYCM-00064,FYCF-00018
it’s working fine but i need the result like this, with quotes around every code 'FYCT-00063','FYCM-00016','FYCM-00064','FYCF-00018'
The query is this:
SELECT DISTINCT SS.PostDate,SS.U_Unit,STUFF(( SELECT ', ',+ CAST(OWOR.ItemCode AS VARCHAR(10)) [text()] FROM OWOR WHERE OWOR.PostDate=SS.PostDate AND OWOR.U_Unit=SS.U_Unit AND OWOR.Status=SS.Status FOR XML PATH('') , TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') LISTGroup from owor SS Where SS.PostDate='15.APRIL.15' AND SS.U_Unit='Unit No 2' and SS.Status!='C'
Advertisement
Answer
Try this
SELECT DISTINCT SS.PostDate ,SS.U_Unit ,STUFF(( SELECT ', ' + QUOTENAME(CAST(OWOR.ItemCode AS VARCHAR(10), '''')) FROM OWOR WHERE OWOR.PostDate = SS.PostDate AND OWOR.U_Unit = SS.U_Unit AND OWOR.STATUS = SS.STATUS FOR XML PATH('') ).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ') LISTGroup FROM owor SS WHERE SS.PostDate = '15.APRIL.15' AND SS.U_Unit = 'Unit No 2' AND SS.STATUS != 'C'