Click here to Skip to main content
11,416,194 members (56,347 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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.4K
Edited 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 at 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 at 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 at 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 at 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
0 Sergey Alexandrovich Kryukov 9,270
1 OriginalGriff 7,512
2 Maciej Los 3,710
3 Abhinav S 3,324
4 Peter Leow 3,084


Advertise | Privacy | Mobile
Web03 | 2.8.150427.4 | Last Updated 30 Nov 2012
Copyright © CodeProject, 1999-2015
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