Hi
I need to produce an excel output from SQL that shows
email - company name - producttype1, producttype2 (where the - denotes
a change in column)

basically i have a table the first line is the fields and the second
and third are the entries
email - companyname - producttype
xx (AT) xx (DOT) com - xx - Product1
xx (AT) xx (DOT) com - xx - Product2


ie I only want 1 row for the email, but want to show the 2 products
they have listed for them.

This is a simplistic version of what I want as there are actually
other fields in the table which I want to display in exactly the same
way, only 1 line per contact.

Can anyone help?

Thanks
Archana

Hi Archana,

I guess the key would be concatenating the various products into a
single line. Here's a basic concatenation function that will get all
your products into a single string, based on email and company:

CREATE FUNCTION dbo.ufn_concatProducts(
@email varchar(255)
, @company varchar(255))
RETURNS varchar(255)
AS
BEGIN
DECLARE @productList varchar(8000)

SELECT
@productList = coalesce(@productList + ',', '') + product
FROM
tbl
WHERE
email = @email AND
company = @company

RETURN(@productList)
END
GO


Now, you just have to query your string together:

SELECT
email + ' - ' +
company + ' - ' +
dbo.ufn_concatProducts(email, company) AS ProductList
FROM
tbl
GROUP BY
email
, company


Have fun!

Ken


On Feb 20, 6:27 pm, archanapatelwh... (AT) googlemail (DOT) com wrote:

KenJ (kenjohnson (AT) hotmail (DOT) com) writes:
Not that this function relies on undefined behaviour. You may get what
you execpt, but everyonce in ahile. you don't.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx