Subquery and display help

[archanapatelwhite@googlemail.com]

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@xx.com - xx - Product1
xx@xx.com - xx - Product2

>From my query I want to show in excel

xx@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

[KenJ]

Re: Subquery and display help

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_concatP roducts(
@email varchar(255)
, @company varchar(255))
RETURNS varchar(255)
AS
BEGIN
DECLARE @productList varchar(8000)

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

RETURN(@product List)
END
GO


Now, you just have to query your string together:

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


Have fun!

Ken


On Feb 20, 6:27 pm, archanapatelwh. ..@googlemail.c om 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

[Erland Sommarskog]

Re: Subquery and display help

KenJ (kenjohnson@hot mail.com) writes:

CREATE FUNCTION dbo.ufn_concatP roducts(
@email varchar(255)
, @company varchar(255))
RETURNS varchar(255)
AS
BEGIN
DECLARE @productList varchar(8000)
>
SELECT
@productList = coalesce(@produ ctList + ',', '') + product
FROM
tbl
WHERE
email = @email AND
company = @company
>
RETURN(@product List)
END

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx