Column concatenation problem - 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 2000 Forums

Transact-SQL (2000)

Column concatenation problem

Author

Topic

kimbelj
Starting Member

13 Posts

Posted - 2004-02-23 : 12:03:16

I have a feeling this has been asked many times before, but I'm looking for pointers or a topic to search on (of course a solution would be even better!):

I have a table of contacts (customers) who each has a number of products that are associated with customers. I can create the following table by joining customers to products:

Cust_Id Customer Email Prod_id Product
------- -------- ----- ------- -------
1 John Smith smith@email.com 1 Product1
1 John Smith smith@email.com 2 Product2
1 John Smith smith@email.com 3 Product3
2 Barb Doe doe@email.com 1 Product1
2 Barb Doe doe@email.com 1 Product2
3 Jane Roberts roberts@email.com 1 Product1
3 Jane Roberts roberts@email.com 1 Product2
4 Rob Simons simons@email.com 4 Product4

I want to produce a table that collects the Customers having the same products so I can send a product update email to each customer in the list. Something like this:

Customer(s) Email(s) Product(s)
----------- -------- ---------
John Smith smith@email.com Prod1, Prod2, Prod3
Barb Doe, Jane Roberts doe@email.com, roberts@email.com Prod1,Prod2
Rob Simons simons@email.com Prod4

Any help is appreciated!

-- Jeff

(Sorry, the spacing is all messed up - why does it remove spaces?)

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 12:24:59

I'd really like to help, but I'm going to give one of those responses that ask for clarification, sorry...

quote:
I want to produce a table that collects the Customers having the same products so I can send a product update email to each customer in the list

The dataset that follows doesn't meet your description of what you want (all customers are listed, with whatever products they have), that - and it's tougher to create that dataset than what you seem to need for a mailing list.

If you want a mailing list for "customers with the same products" how about a query returning the email addresses for customers with Product N?

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 12:28:02

quote:

(Sorry, the spacing is all messed up - why does it remove spaces?)

Bracket your code with [ code]your code here [ /code]

remove the spaces.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 12:34:34

This borrows from this article....

http://www.sqlteam.com/item.asp?ItemID=2368


USE Northwind
GO

SET NOCOUNT ON

CREATE TABLE myTable99(
	  Cust_Id int
	, Customer varchar(50)
	, Email varchar(50)
	, Prod_id int
	,  Product varchar(50))
GO

INSERT INTO myTable99(Cust_Id, Customer, Email, Prod_id, Product)
SELECT 1, 'John Smith',   'smith@email.com',   1, 'Product1' UNION ALL
SELECT 1, 'John Smith',   'smith@email.com',   2, 'Product2' UNION ALL
SELECT 1, 'John Smith',   'smith@email.com',   3, 'Product3' UNION ALL
SELECT 2, 'Barb Doe',     'doe@email.com',     1, 'Product1' UNION ALL
SELECT 2, 'Barb Doe',     'doe@email.com',     2, 'Product2' UNION ALL
SELECT 3, 'Jane Roberts', 'roberts@email.com', 1, 'Product1' UNION ALL
SELECT 3, 'Jane Roberts', 'roberts@email.com', 2, 'Product2' UNION ALL
SELECT 4, 'Rob Simons',   'simons@email.com',  4, 'Product4'
GO

CREATE TABLE #myTemp99(Cust_Id int, Products varchar(8000))

DECLARE @Cust_Id int, @Products varchar(8000)

DECLARE myCursor99 CURSOR
FOR SELECT DISTINCT Cust_Id FROM myTable99

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @Cust_Id

WHILE @@FETCH_STATUS = 0
  BEGIN

	SELECT @Products = COALESCE(@Products + ', ', '') + 
	   CAST(Product AS varchar(50))
	  FROM myTable99 WHERE Cust_Id = @Cust_Id

	INSERT INTO #myTemp99(Cust_Id, Products) SELECT @Cust_Id, @Products
	SELECT @Products = NULL
	FETCH NEXT FROM myCursor99 INTO @Cust_Id
  END

CLOSE myCursor99
DEALLOCATE myCursor99

SELECT DISTINCT Customer, Email, Products
  FROM myTable99 l INNER JOIN #myTemp99 r ON l.Cust_Id = r.Cust_Id 
ORDER BY l.Customer
GO

DROP TABLE #myTemp99
DROP TABLE myTable99
GO

SET NOCOUNT OFF
GO

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 12:38:38

The requirement is return a distinct set of email addresses for any set of Product1...ProductN.

Can't this be done without cursing?

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 12:43:32

quote:
Originally posted by SamC

The requirement is return a distinct set of email addresses for any set of Product1...ProductN.

Can't this be done without cursing?

I tried to do it without a F%@#@& Cursor, But I'm so G$#^% D(*&^&* F&*(^&^ stupid that I Gave the F^%* UP.

So no, I guess I have to F^%$&^% Curse...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-23 : 12:53:29

isn't it just:

select distinct customer, email, prodID
from yourtable

??

That returns 1 row per customer and per prodID. just filter by the ProdID you want.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 12:55:25

He wants all the products on 1 row....


Customer(s) Email(s) Product(s)
----------- -------- ---------
John Smith smith@email.com Prod1, Prod2, Prod3
Barb Doe, Jane Roberts doe@email.com, roberts@email.com Prod1,Prod2
Rob Simons simons@email.com Prod4

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 13:06:48

It's just WRONG ! Wrong !

I gotta have a margarita.

jbkayne
Posting Yak Master

100 Posts

Posted - 2004-02-23 : 13:09:14

You would have to create a UDF then call it from the distinct set.

i.e.

select distinct
customer
, email
, dbo.fnc_ConcateProductsByCustId(Cust_Id) as Product

If you need help on the UDF let me know

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 13:09:31

I *think* that he wants to specify a unique set of products for each mailing, so the query would be:

SELECT DISTINCT customer, email
FROM MyTable
WHERE Product IN ('Product1', 'Product5', 'ProductN')

The only difficulty is accepting a CSV list of products and building a recordset for the IN clause. Maybe SQL's IN Clause should be extended to include a Santa Clause?

kimbelj
Starting Member

13 Posts

Posted - 2004-02-23 : 13:18:12

Thanks for all the help! I walked away and didn't expect such an immediate response. I can try and be more specifc. The client I'm working for wants to be able to select (through an interface I'm creating) product updates to send to customers that all get the same product update. They want to be able to cut and paste the email list into their email and then attach the appropriate product update. So I need to gather all the customers that requre the same product updates and have the same products.

In the example both Jane and Barb have the same product list and would get the same updates. John and Rob are separated since they don't have a product list in common. I didn't get to look at the cursor example too closely but if it does the trick, I have no qualms about using cursors (they want the solution tomorrow).

-- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 13:27:12

Did you cut and paste the code?

Just open an QA Window and let it rip...

Should be fully functional, and you caqn see the sample results...

Sounds like you want something a little different though

Sounds like you want to build distribution lists....

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 13:34:34

quote:
I walked away and didn't expect such an immediate response.

Proposed SQLTeam motto: When you post, we'll roast, so don't coast.

I'd better not quit my day job.

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 13:36:32

The requirement is still vague to me. Do your customers want to input a product set and retrieve all email addresses that exactly match that product set?

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 13:48:55

I think I got it...Sam cut and paste the code and test it....

The second part is what was missing...



USE Northwind
GO

SET NOCOUNT ON

CREATE TABLE myTable99(
	  Cust_Id int
	, Customer varchar(50)
	, Email varchar(50)
	, Prod_id int
	,  Product varchar(50))
GO

INSERT INTO myTable99(Cust_Id, Customer, Email, Prod_id, Product)
SELECT 1, 'John Smith',   'smith@email.com',   1, 'Product1' UNION ALL
SELECT 1, 'John Smith',   'smith@email.com',   2, 'Product2' UNION ALL
SELECT 1, 'John Smith',   'smith@email.com',   3, 'Product3' UNION ALL
SELECT 2, 'Barb Doe',     'doe@email.com',     1, 'Product1' UNION ALL
SELECT 2, 'Barb Doe',     'doe@email.com',     2, 'Product2' UNION ALL
SELECT 3, 'Jane Roberts', 'roberts@email.com', 1, 'Product1' UNION ALL
SELECT 3, 'Jane Roberts', 'roberts@email.com', 2, 'Product2' UNION ALL
SELECT 4, 'Rob Simons',   'simons@email.com',  4, 'Product4'
GO

CREATE TABLE #myTemp99(Cust_Id int, Products varchar(8000))

DECLARE @Cust_Id int, @Products varchar(8000)

DECLARE myCursor99 CURSOR
FOR SELECT DISTINCT Cust_Id FROM myTable99

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @Cust_Id

WHILE @@FETCH_STATUS = 0
  BEGIN

	SELECT @Products = COALESCE(@Products + ', ', '') + 
	   CAST(Product AS varchar(50))
	  FROM myTable99 WHERE Cust_Id = @Cust_Id

	INSERT INTO #myTemp99(Cust_Id, Products) SELECT @Cust_Id, @Products
	SELECT @Products = NULL
	FETCH NEXT FROM myCursor99 INTO @Cust_Id
  END

CLOSE myCursor99
DEALLOCATE myCursor99

SELECT DISTINCT Customer, Email, Products
  FROM myTable99 l INNER JOIN #myTemp99 r ON l.Cust_Id = r.Cust_Id 
ORDER BY l.Customer
GO

CREATE TABLE #myTemp00(ProductList varchar(1000), EmailList varchar(7000))

DECLARE @ProductList varchar(1000), @EmailList varchar(7000)

DECLARE myCursor00 CURSOR
FOR SELECT DISTINCT Products FROM #myTemp99

OPEN myCursor00

FETCH NEXT FROM myCursor00 INTO @ProductList

WHILE @@FETCH_STATUS = 0
  BEGIN

	SELECT @EmailList = COALESCE(@EMailList + ', ', '') + 
	   CAST(EMail AS varchar(50))
	  FROM #myTemp99 l INNER JOIN (SELECT DISTINCT Cust_Id, Email FROM myTable99) AS r
	    ON l.Cust_Id = r.Cust_Id
	 WHERE Products = @ProductList

	INSERT INTO #myTemp00(ProductList, EmailList) SELECT @ProductList, @EMailList
	SELECT @EMailList = NULL
	FETCH NEXT FROM myCursor00 INTO @ProductList
  END

CLOSE myCursor00
DEALLOCATE myCursor00

SELECT * FROM #myTemp00
GO


DROP TABLE #myTemp00
DROP TABLE #myTemp99
DROP TABLE myTable99
GO

SET NOCOUNT OFF
GO

Brett

8-)

kimbelj
Starting Member

13 Posts

Posted - 2004-02-23 : 13:54:21

Yes, I'm creating distribution lists by product group. The client has already associated products with customers and thats captured in the database. I'm not keeping up here (lol), I'll try the lastest Northwinds posting. Thanks again for all the help!!!

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-23 : 13:55:56

Everybody get's it but me. I'm gonna flip burgers.

The requirement statement says his customer wants to cut and paste a specific set of email addresses from the database for a product update delivery, so it implies that only records (email addresses) matching a specific criteria are returned. This much makes sense. I'd like to see the criteria expressed as Produc IDs, not

quote:
customers that all get the same product update

The criteria in terms of Product ID's hasn't been posted - only what makes two customers similar, and that isn't a criteria.

The only sample recordset (in the first post) shows all customers returned.

kimbelj
Starting Member

13 Posts

Posted - 2004-02-23 : 13:58:45

Sam, the product groups are created on the fly. So they will set up a number of customers with any number of products some customers might have the same products, others might have others and overlap. Does this make sense?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-23 : 14:04:15

quote:
Originally posted by SamC
Proposed SQLTeam motto: When you post, we'll roast, so don't coast.

I LIKE IT !!!!

kimbelj
Starting Member

13 Posts

Posted - 2004-02-23 : 14:10:05

Sam, I'm not exactly sure what you are asking, but the qualification for each group of customers is that they have an associated list of products and this list is the same for each customer in the group.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources