Convert row with text field to column

Hi experts;

How can I generate the result using typical SQL statement based on the following tables?

Table a - Salesman (salesId, Name) pk : salesId

Table b - Invoice(InvoiceNo, salesId, InvoiceAmt) pk : invoiceNo fk : salesId -> Table a

The result set :

salesId, Name, sum(InvoiceAmt), InvoiceNos with comma separator

For example:

Table a

SalesId Name

S001 Peter

S002 Alice

Table b

InvoiceNo SalesId InvoiceAmt

INV001 S001 $100

INV002 S001 $100

INV003 S001 $400

INV004 S002 $200

Result set

SalesId Name Sum(InvoiceAmt) InvoiceNos

S001 Peter $600 INV001, INV002, INV003

S002 Alice $200 INV004

Thanks

Hi,

You can use the COALESCE function in order to get columns of more than one row as a string expression.

You can find samples at below links.

http://www.kodyaz.com/articles/article.aspx?articleid=29
http://www.kodyaz.com/forums/thread/76.aspx

It is better to create a user defined function which uses the Coalesce and then call this function in your select queries to get related list of invoice no's as one string seperated with commas.

A sample function may be like the below one.

CREATE FUNCTION udf_GetFileList

(

@.RelatedPKID int

)

RETURNS nvarchar(4000)

AS

BEGIN

Declare @.Files nvarchar(4000)

SELECT

@.Files = COALESCE(@.Files + ' ' ,'') + SystemFileName + ','

FROM

AttachedFiles

WHERE

RelatedPKID = @.RelatedPKID

RETURN @.Files

END

GO

I hope this helps.

Eralper

http://www.kodyaz.com

|||

Check out the techniques in this article:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

The "select @.variable = @.variable + columnValue" approach has its dangerous limitations, and should be avoided if possible (It is a neat trick that I have used too, so I am not trying to moralize, just note the issues :)

In 2005, however, there is a much better way of doing things using the XML, functionality, which is outlined in that article.

|||

Hi Eralper;

Thank you. It worked.

You see my SQL as follows:

create function get_invlist
( @.SalesID NvarChar(10))
Returns nvarchar(4000)
as begin

declare @.inv NvarChar(4000);
select @.inv=COALESCE(@.inv + '','')+InvoiceId +',' from sales, invoice
where
sales.salesid = invoice.salesid
and sales.salesid = @.SalesID
Return @.inv
end
go

end function

select sales.salesid, salesName, sum(invamt), test.dbo.get_invlist(sales.salesid)
from sales, invoice
where
sales.salesid = invoice.salesid
group by sales.salesid, salesname;

Result

S0001 Peter 600.00 INV001 ,INV002 ,INV003 ,
S0002 Alice 500.00 INV004 ,

Regards.

|||

Hi Louis;

I agreed you that using @.variable will have limitations. I will test your suggestion later.

Could I use classical SQL to implement this kind of requirement? (i.e. not use @.variable or XML or new function)

Thanks

|||

Hi Louis;

I tried your method. It is better than before.

Please see my SQL.

select sales.salesid, salesName,
Replace((select rtrim(InvoiceId)
as "data()"
from invoice
where
sales.salesid = invoice.salesid
for XML PATH ('')), ' ', ',')
as "@.InvID"
from sales
group by sales.salesid, salesname;

It is so dynamic and easy to understand.

Thanks.

|||I wish it was my method :) It was Aaron Bertrand's. Glad it helped.