Many values from a single field returned into one single string

Many values from a single field returned into one single string

Post by Nicolas Verhaegh » Fri, 22 Aug 2003 02:38:15



Is there a way to return the values from a single field into a CSV string
(say: "3,5,7,8") without creating a cursor?

Right now I do that with a cursor and add the values from that one field one
by one, with a comma prior to that, only when the string is not empty.

Is there any shortcut that would allow me to return these values in a string
inside of a single SELECT statement?

Or is a cursor my only way to do that?

Thanks in advance!

 
 
 

Many values from a single field returned into one single string

Post by Vishal Parka » Fri, 22 Aug 2003 02:48:57


The info. you have specified is not sufficient.
is this a single column from which you are retrieving the values. If yes
what is the replacement holder for "," . i mean are the spaces in the string
needs to be replaced by commas ?
If yes, then you can have something like this.

select replace(column1, ' ', ',') from table

--
-Vishal

Quote:> Is there a way to return the values from a single field into a CSV string
> (say: "3,5,7,8") without creating a cursor?

> Right now I do that with a cursor and add the values from that one field
one
> by one, with a comma prior to that, only when the string is not empty.

> Is there any shortcut that would allow me to return these values in a
string
> inside of a single SELECT statement?

> Or is a cursor my only way to do that?

> Thanks in advance!


 
 
 

Many values from a single field returned into one single string

Post by Art Coru » Fri, 22 Aug 2003 03:34:30


--Maybe something like the following:




        from    master.dbo.systypes




Quote:>-----Original Message-----
>Is there a way to return the values from a single field
into a CSV string
>(say: "3,5,7,8") without creating a cursor?

>Right now I do that with a cursor and add the values from
that one field one
>by one, with a comma prior to that, only when the string
is not empty.

>Is there any shortcut that would allow me to return these
values in a string
>inside of a single SELECT statement?

>Or is a cursor my only way to do that?

>Thanks in advance!

>.

 
 
 

Many values from a single field returned into one single string

Post by Nicolas Verhaegh » Fri, 22 Aug 2003 20:47:15


I do not think you took the time to read "the info" I specified.

The comma is there to separate values, hence it being called a "CSV".

Quote:> The info. you have specified is not sufficient.
> is this a single column from which you are retrieving the values. If yes
> what is the replacement holder for "," . i mean are the spaces in the
string
> needs to be replaced by commas ?
> If yes, then you can have something like this.

> select replace(column1, ' ', ',') from table

 
 
 

Many values from a single field returned into one single string

Post by Nicolas Verhaegh » Fri, 22 Aug 2003 20:47:16


I will reformulate my question:

Say I have a table called "myTable" with a field called "myValue":

The Statement:

SELECT MyValue
FROM MyTable
ORDER BY MyValue

Gives:

MyValue
-------
2
5
7
8

What I am trying to know is if there is a way to get this:

... without using a Cursor but rather a SINGLE SELECT statement.

If not, then I will use a cursor.

Thanks in advance for your help!

 
 
 

Many values from a single field returned into one single string

Post by oj » Fri, 22 Aug 2003 20:54:05




from
(select top 100 percent employeeid
from northwind..employees
order by employeeid)x


--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


> I will reformulate my question:

> Say I have a table called "myTable" with a field called "myValue":

> The Statement:

> SELECT MyValue
> FROM MyTable
> ORDER BY MyValue

> Gives:

> MyValue
> -------
> 2
> 5
> 7
> 8

> What I am trying to know is if there is a way to get this:


> ... without using a Cursor but rather a SINGLE SELECT statement.

> If not, then I will use a cursor.

> Thanks in advance for your help!

 
 
 

Many values from a single field returned into one single string

Post by nav » Sat, 23 Aug 2003 16:57:04




student s1

>-----Original Message-----


>from
>(select top 100 percent employeeid
>from northwind..employees
>order by employeeid)x


>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net




>> I will reformulate my question:

>> Say I have a table called "myTable" with a field
called "myValue":

>> The Statement:

>> SELECT MyValue
>> FROM MyTable
>> ORDER BY MyValue

>> Gives:

>> MyValue
>> -------
>> 2
>> 5
>> 7
>> 8

>> What I am trying to know is if there is a way to get
this:


>> ... without using a Cursor but rather a SINGLE SELECT
statement.

>> If not, then I will use a cursor.

>> Thanks in advance for your help!

>.

 
 
 

Many values from a single field returned into one single string

Post by Nicolas Verhaegh » Sun, 24 Aug 2003 01:19:36


Thanks.

This returns ,2,3,5,6,8

I guess I can remove the first character by using a substring?

Otherwise what is the trailing "x" after the closing parenthesis?

Thanks for your help!




> from
> (select top 100 percent employeeid
> from northwind..employees
> order by employeeid)x


> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net



> > I will reformulate my question:

> > Say I have a table called "myTable" with a field called "myValue":

> > The Statement:

> > SELECT MyValue
> > FROM MyTable
> > ORDER BY MyValue

> > Gives:

> > MyValue
> > -------
> > 2
> > 5
> > 7
> > 8

> > What I am trying to know is if there is a way to get this:


> > ... without using a Cursor but rather a SINGLE SELECT statement.

> > If not, then I will use a cursor.

> > Thanks in advance for your help!

 
 
 

1. Help with listing Universe multi-value field and single field

Hello Everybody,

Does anybody know how to list a Universe file with single value attribute
to repeat when there are multiple multi-value fields.

For example:

LIST FILE1 SINGLE.FIELD1 MULTI-VALUE.FIELD2 would result with:

SINGLE.FIELD1  MULTIVALUE.FIELD2
s1             m1.1
s1             m1.2
s2             m2.1

Thank a lot for any help.
--
--
Peter T. Tran
PPG Industries, Inc.

2. Insert Statement help

3. Empty strings returned as a single space

4. BUTIL -RECOVER under Btrieve 7.01 ?

5. Is it possible to return a recordset as a single comma separated string of characters

6. US-CA-MEMBER OF TECHNICAL STAFF (SR. INFOCENTER ANALYST)

7. QueHelp with Query, return a single string

8. SQL Statement syntax with Dutch Locale settings

9. ORA-01427: single-row subquery returns more than one row

10. Dreaded ORA-01427: single-row subquery returns more than one row error

11. Single Quotes within string values

12. sql question for single row query returns more than one row

13. Splitting out values from a single varchar string