Posted by KenJ on 02/22/07 04:00

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...@googlemail.com wrote:
> 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
> x...@xx.com - xx - Product1
> x...@xx.com - xx - Product2
>
> >From my query I want to show in excel
>
> x...@xx.com - xx - Product1, 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

[Back to original message]