select multiple values into a single field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select multiple values into a single field

Author

Topic

casigrl
Starting Member

4 Posts

Posted - 2004-10-04 : 13:53:38

Hello. I'm new here. Here's my problem.

I need to select several values and insert them (as an array?) into one field. This is my code:

select o.OPPORTUNITYID,p.NAME
from SYSDBA.PRODUCT P
join SYSDBA.OPPORTUNITY_PRODUCT O on o.PRODUCTID = P.PRODUCTID
and o.OPPORTUNITYID = 'O0BK5A300001'
group by o.OPPORTUNITYID

now how do I save the results into a single value? Thanks so much!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 13:56:18

select convert(varchar(10), o.OPPORTUNITYID) + ',' + p.NAME
from SYSDBA.PRODUCT P
join SYSDBA.OPPORTUNITY_PRODUCT O on o.PRODUCTID = P.PRODUCTID
and o.OPPORTUNITYID = 'O0BK5A300001'
group by o.OPPORTUNITYID

Tara

casigrl
Starting Member

4 Posts

Posted - 2004-10-04 : 14:07:57

Tara = thank you!

I'm definitely NOT a sql expert; does this portion:

"convert(varchar(10), o.OPPORTUNITYID) + ',' +"

take all my Opportuniy ids and create a list/array of them? I'm just not clear on that...thanks...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 14:12:33

Since OPPORTUNITYID is probably an integer column, we have to convert it to varchar first in order to concatenate it in a string.

If you have another column say Table1_ID and it's an integer column as well and you try to concatenate them together in a string like this:

SELECT opportunityID + Table1_ID

you'll get the two added together not concatenated together. So you get addition. If you want to concatenate them, you'd need to convert them to varchar and then do +.

Tara

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-04 : 14:27:37

quote:
Originally posted by casigrl

Tara = thank you!

I'm definitely NOT a sql expert; does this portion:

"convert(varchar(10), o.OPPORTUNITYID) + ',' +"

take all my Opportuniy ids and create a list/array of them? I'm just not clear on that...thanks...

IT CONCATENATES OPORTUNITYID WITH A COMMA AND WITH THE NAME AFTER CONVERTING OPORTUNITYID TO A STRING OF VARIABLE LENGTH WITH A LIMIT OF 10 CHARACTERS AT MOST

IF THE FIRST p.NAME='OPPNAME' THEN
THE FIRST RESULT WILL HAVE IN ONE FIELD
'O0BK5A3000,OPPNAME'

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-10-04 : 14:28:34

I may be missing something, but wouldn't that statement fail due to items in the select not being contained in either the group by or an aggregate function?
casigrl, do you want a 1-row, 1-column resultset?

Mark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 14:31:19

Ah yes, didn't see the GROUP BY. Then use byrmol's UDF solution:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

Tara

casigrl
Starting Member

4 Posts

Posted - 2004-10-04 : 14:32:19

Crap. I totally messed up in presenting my problem.

Basically, we only selected the OpportunityID for our purposes at the moment. It's not actually in the query. This is what it's going to look like:

select p.NAME
from SYSDBA.PRODUCT P
join SYSDBA.OPPORTUNITY_PRODUCT O on o.PRODUCTID = P.PRODUCTID
and o.OPPORTUNITYID = 'O0BK5A300001'
group by p.NAME

My problem is that this query returns several names. I need to stick all those names into one field. Sorry that I wasn't clear enough before, but if you could help me out a bit more, I'd very much appreciate it. Thanks so much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-04 : 14:33:10

Check out byrmol's UDF solution in the link that I provided.

Tara

casigrl
Starting Member

4 Posts

Posted - 2004-10-04 : 14:34:56

Yep, looking at it now. I think that's what I need. Thank you all very much!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources