Click here to Skip to main content
13,250,182 members (27,853 online)
Rate this:
 
Please Sign up or sign in to vote.
I want to concatenate one column values. So I give following, But it is not working the error displays.
SELECT STUFF((SELECT ',' + scd_service_id FROM Service_Details FOR XML PATH(")),1,2,")

The error is
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')),1,2,'.
Posted 27-Nov-12 18:21pm
devausha1.5K
Updated 27-Nov-12 18:50pm
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

It seems you have used double quote" instead of two single qoutes''. check updating your query with new query as shown below

your query :
SELECT STUFF((SELECT ',' + scd_service_id FROM Service_Details FOR XML PATH(")),1,2,")


new query :
SELECT STUFF((SELECT ',' + scd_service_id FROM Service_Details FOR XML PATH('')),1,2,'')
  Permalink  
Comments
devausha 29-Nov-12 23:25pm
   
Thanks for your reply. I try your new query, But the error displays like

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Do casting as shown below.

new query :
SELECT STUFF((SELECT ',' + ltrim(cast(scd_service_id as varchar(20))) FROM Service_Details FOR XML PATH('')),1,2,'')
  Permalink  
Comments
devausha 30-Nov-12 7:51am
   
Hi Thank you so much...
I am used following query for matching name from another table. It is also working. But the first letter is not displaying
My query is

SELECT STUFF((SELECT ',' + ltrim(T1.service_name) FROM Service_Details T2 Left outer join Service_master T1 on T2.scd_service_id=T1.service_id where T2.scd_jc_id=5 FOR XML PATH('')),1,2,'')

The result is 'arwashing project,Welding'

But actual result is 'Carwashing project,Welding'
devausha 30-Nov-12 7:52am
   
Why it comes without first letter?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Try replacing SELECT ',' with SELECT ', '. Give one space after comma.
  Permalink  
v2
Comments
devausha 1-Dec-12 3:39am
   
Hi, Thank u so much...It is working correctly. Very nice...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.171114.1 | Last Updated 30 Nov 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100