Returning a String of Value - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Returning a String of Value

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-29 : 11:15:03

Hi

In the database I am using there is an option for a person to choose multiple options ie. a person choosing a form of conact, such as fone and mail.
When I search this information it will show two records for the person each one showing the different contact method. Is there a function that returns a string of values that have been selected on one line, so there is no need for there to be multiple records?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-29 : 11:41:24

suppose you have person table as
Person(PersonID,PersonName)
and contact as
Contacts(ContactID,ContactName,PersonID)

using this query...

select p.PersonName,
LEFT(ContactList,LEN(ContactList)-1) AS 'Contacts'
-- strip the last ',' from the list
from
Person p cross apply
(select c.ContactName + ',' as [text()]
from Contacts c
where c.PersonID = p.PersonID
order by c.ContactName
for xml path('')) Temp(ContactList)
go

you will get result as

PersonName Contacts
P1 phone
P2 phone,mail
...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-30 : 01:36:04

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources