We help IT Professionals succeed at work.

TSQL - How to get output results in row with additional character(comma)

Darius
Darius asked
on
67 Views
Last Modified: 2017-04-19
Hi Guys,

I have hundreds doc id's in output which I need manually modify into row, separated with comma. It works to me with additional tools like notepad++.

Can you assist with modification of sql query to retrieve results in row without additional tools.

SELECT documentid FROM Documents 
output result like:
1178211
1178209
1178208
1178207
1178203
1178202
1178201
1178200
1178196
1178195

to output results like:
1178211,1178209,1178208,1178207,1178203,1178202,1178201,1178200,1178196,1178195

Open in new window


Thank you,
Darius
Comment
Watch Question

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you Vitor!
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Oh, if documentid is a numeric then you'll need to convert it to string:
SELECT STUFF((SELECT ',' + CAST(documentid AS VARCHAR)
FROM Documents
FOR XML PATH('')), 1, 1, '')

Open in new window

Author

Commented:
I did my self and it works :)

Author

Commented:
SELECT STUFF((SELECT ' ,' + cast(documentid as varchar)
FROM documents
ORDER BY documentid Desc
FOR XML PATH('')), 1, 1, '')

Open in new window

One more question...
How to avoid comma as first character
output:
,219 ,220 ,221 ,222 ,223

Thank you
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Remove the extra space (my fault since I add a space before the comma) in the 2nd select:
SELECT STUFF((SELECT ',' + CAST(documentid AS VARCHAR)
FROM Documents
FOR XML PATH('')), 1, 1, '')

Open in new window

Author

Commented:
Perfect! Thank you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.