• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

return results in one string

My query returns a results like below.

select itemno from mytable where itemno between 9813 and 9865.

result :

9813
9814
9815
etc...

Can it be returned in one string with comma like below?

9813, 9814, 9815, etc
0
VBdotnet2005
Asked:
VBdotnet2005
3 Solutions
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Please try this

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + convert(varchar,itemno )
FROM mytable where itemno between 9813 and 9865
SELECT @listStr

Open in new window

0
 
chaauCommented:
If you calling this query from a VB app you may not be able to use DECLARE syntax. In this case, use FOR XML_PATH, like this:
SELECT      
            STUFF((    SELECT ',' + CAST(itemNo AS VARCHAR(30)) AS [text()]
                        FROM mytable
                        WHERE
                        itemno between 9813 and 9865
                        FOR XML PATH('')
                        ), 1, 1, '' ) AS items

Open in new window

0
 
PortletPaulfreelancerCommented:
or this:
SELECT
     STUFF(
             (
              SELECT
                    ', ' +  convert(varchar,itemno )
              FROM yourtable
              WHERE itemno between 9813 and 9865
              FOR XML PATH ('')
              )
           , 1, 1, '')
;

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
chaauCommented:
@PortletPaul: beat you by exactly 14 seconds
0
 
PortletPaulfreelancerCommented:
@chaau, yes, so I see, too much fussing with the format at my end :)
0
 
nemws1Database AdministratorCommented:
I wish MSSQL had an easy to use GROUP_CONCAT() function like MySQL. :)
0
 
PortletPaulfreelancerCommented:
or LISTAGG() in Oracle

I agree with you. The syntax of stuff() plus "for xml path" is a bit tedious and a bit tricky.
(Hence my fixation with formatting it :)
0
 
chaauCommented:
Concat_ws is better in my opinion. Works in Oracle and MySQL
0
 
Anthony PerkinsCommented:
I wish MSSQL had an easy to use GROUP_CONCAT() function like MySQL. :)
You have a couple of options:
1. Use CLR to build your own.  It is not difficult and can be more flexible.
2. Be patient. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now