Stored Procedure + inner join + combining columns

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)

Stored Procedure + inner join + combining columns

Author

Topic

gdong
Starting Member

1 Post

Posted - 2010-02-24 : 19:21:26

Hi All,

I have a table Contacts, which contains a list of address book contacts. One of the fields is called ContactLabelID, which is just an integer which links to an ID field in the ContactLabels table. Within the ContactLabels table, I can have several rows that are associated with a row in the Contact table, each with a unique label.

Now if I just do a plain inner join on the Contacts and ContactLabels tables on the ContactLabelID field, I will have duplicate contact rows, each with a unique label field. How do I go about writing a stored procedure where I can query all the contacts, and have the contact labels as a new, comma separated column in the resulting data set?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-24 : 20:07:25

Could you show us a data example to make it more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Sachin.Nand

2937 Posts

Posted - 2010-02-24 : 21:48:08

Something like this


select id,
(select contactlables + ',' from contactlables where contactlables.contactid=contact.contactid for XML path(''))as contactlables
from contact

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources