CODEWITHSUNDEEP
sqlsql-server
georg9
georg9

Reputation: 11

SQL query answer

Using SQL I am selecting the following:

SELECT itemid, custid, email FROM tableA;

I can have multiple itemid for each one Custid and only one email like

itemid1, custid, email
itemid2, custid, email
itemid3, custid, email

How can i send only one email per customer specified that he has 3 itemid?

thanks

Upvotes: 0

Views: 85

Answers (2)

Jen R
Jen R

Reputation: 1535

select count(itemid), custid, email 
from tableA
group by email, custid

After clarification that the OP is joining tables and wants a list of items in one row with the email address, we have this query:

select b.custid, b.email, 
   itemids = STUFF((select ',' + itemid 
             from tableA a 
             where a.custid = b.custid FOR XML PATH('')), 1, 1, '') 
from tableB b

This will do it, but if you need more info about the items I would strongly recommend getting a list of customers and then looping through them to get the item info as you go.

Upvotes: 1

Andrey Baulin
Andrey Baulin

Reputation: 679

You could use function like this for all your custid and email :

ALTER        FUNCTION itemrow(@custid int, @email varchar(max)) --check your datatypes
RETURNS VARCHAR(max) AS
BEGIN
  DECLARE @itemrow VARCHAR(max)
  SELECT  @itemrow = COALESCE(@itemrow + ', ', '') + COALESCE(itemid,'')
  FROM tableA
  where email = @email
  and custid = @custid
return @itemrow
END

Upvotes: 0

Related Questions