Need Help with Query?

Question

Post reply

Need Help with Query?

rajagopalanseeth

SSC Enthusiast

Points: 152
More actions
February 13, 2010 at 10:54 pm

#210875

Hi,
I am having the following table
Contacts
---------
Contactid, ContactName
Emailaddress
------------
emailid, emailaddress, isprimary
Email_addr_contacts_rel
-----------------------
id, emailaddress_id, contacts_id
Targets
-------
Targetid, Targetname, date_created
Target_contacts_rel
-------------------
id, Target_id, contact_id.
I can store multiple contact for a single targetid in Target_contacts_rel table. . In the email_addr_rel, it will have emailaddress_id and contact_id. I can store multiple emailaddress for a single contact. What is my requirement is that for each targetid, i want to get the emailaddress for the contacts that is present for that targetid. I want to write a select where it will show TargetId, emailaddress in 1 single row ie.., i want the to store emailaddress in a comma seperated for all the contacts of a Targetid in a single row. How to do it?
Thanks.
Raja

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Please provide sample data.

Also needed is an example showing what your desired result should look like.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rajagopalanseeth SSC Enthusiast Points: 152 More actions · Answer 2

Hi,

Please find the query below

Select targets.name, Emailaddress.emailaddress from targets join Target_contacts_rel on Target_contacts_rel.target_id = targets.targetid join Email_addr_contacts_rel on Email_addr_contacts_rel.contacts_id = Target_contacts_rel.contact_id join Emailaddress on Email_addr_contacts_rel.emailaddress_id = Emailaddress.emailid.

In the above query, if for an example, for 1 targetid we have 5 contacts and for each contacts we have 3 emailaddress. So it will return 15 rows for 1 targetid. Instead of returning 15 rows, i want to return only 1 row that has the targetid and all the emailaddress as a comma seperated values. Can we do it using a Select statement.

Thanks,

Raja

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Please read the article by Gail Shaw in my signature block.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

naveena 78933 SSC Enthusiast Points: 101 More actions · Answer 4

naveena 78933

SSC Enthusiast

Points: 101

February 16, 2010 at 5:13 am

#1119182

select cast(column name as int) from table name

naveena 78933 SSC Enthusiast Points: 101 More actions · Answer 5

naveena 78933 (2/16/2010)
select cast(column name as int) from table name

Paul White SSC Guru Points: 150467 More actions · Answer 6

DECLARE @PoorSampleDataContact

TABLE (

contact_id INTEGER PRIMARY KEY,

email_address VARCHAR(30) NOT NULL

);

DECLARE @PoorSampleDataTarget

TABLE (

target_id INTEGER PRIMARY KEY

);

DECLARE @PoorSampleDataMap

TABLE (

target_id INTEGER NOT NULL,

contact_id INTEGER NOT NULL

);

INSERT @PoorSampleDataTarget (target_id) VALUES (1);

INSERT @PoorSampleDataTarget (target_id) VALUES (2);

INSERT @PoorSampleDataTarget (target_id) VALUES (3);

INSERT @PoorSampleDataContact (contact_id, email_address) VALUES (1, 'email 1');

INSERT @PoorSampleDataContact (contact_id, email_address) VALUES (2, 'email 2');

INSERT @PoorSampleDataContact (contact_id, email_address) VALUES (3, 'email 3');

INSERT @PoorSampleDataContact (contact_id, email_address) VALUES (4, 'email 4');

INSERT @PoorSampleDataContact (contact_id, email_address) VALUES (5, 'email 5');

INSERT @PoorSampleDataMap (target_id, contact_id) VALUES (1, 1);

INSERT @PoorSampleDataMap (target_id, contact_id) VALUES (1, 2);

INSERT @PoorSampleDataMap (target_id, contact_id) VALUES (1, 3);

INSERT @PoorSampleDataMap (target_id, contact_id) VALUES (1, 5);

INSERT @PoorSampleDataMap (target_id, contact_id) VALUES (2, 2);

INSERT @PoorSampleDataMap (target_id, contact_id) VALUES (2, 4);

INSERT @PoorSampleDataMap (target_id, contact_id) VALUES (1, 5);

-- Shows the raw data and mapping

--SELECT T.target_id, C.email_address

--FROM @PoorSampleDataMap M

--JOIN @PoorSampleDataContact C ON C.contact_id = M.contact_id

--JOIN @PoorSampleDataTarget T ON T.target_id = M.target_id

SELECT T.target_id,

STUFF(CA.concatenated, 1, 1, '') AS email_addresses

FROM @PoorSampleDataTarget T

CROSS

APPLY (

SELECT DISTINCT ',' + C.email_address

FROM @PoorSampleDataMap M

JOIN @PoorSampleDataContact C ON C.contact_id = M.contact_id

WHERE M.target_id = T.target_id

FOR XML PATH('')

) CA (concatenated);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi