Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • 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
 
PortletPaulCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
chaauCommented:
@PortletPaul: beat you by exactly 14 seconds
0
 
PortletPaulCommented:
@chaau, yes, so I see, too much fussing with the format at my end :)
0
 
nemws1Commented:
I wish MSSQL had an easy to use GROUP_CONCAT() function like MySQL. :)
0
 
PortletPaulCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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