"List" Function equivalent -- sql server new users

John Stender
3/8/2005 3:45:03 PM

Here is the Sybase help description:

LIST function [Aggregate]

--------------------------------------------------------------------------------

Function
Returns a comma-separated list of values

Syntax
LIST ( { string-expression | DISTINCT column-name } [ , delimiter-string ] )

Parameters
string-expression A string, usually a column name. For each row, the
expression's value is added to the comma-separated result.

DISTINCT column-name The name of a column that you are using in the
query. For each unique value of that column, the value is added to the
comma-separated result.

delimiter-string This optional argument specifies a delimiter string for
the list items. The default setting is a comma. If a value of NULL, or an
empty string is supplied, there is no delimiter. The delimiter-string should
be a constant.

Example
The following statement returns the value 48 Kennedy Court, 54 School Street.

SELECT LIST( street ) FROM employee
WHERE emp_fname = 'Thomas'
Usage
NULL values are not added to the list. List(X) returns the concatenation
(with delimiters) of all the non-NULL values of X for each row in the group.
If there does not exist at least one row in the group with a definite
X-value, then LIST(X) returns the empty string.

--------------------------
So for example in our database an account can have multiple plants, to get a
list of all of the plants associated with a specific account the following
SQL select statement

select a.account_id, list(distinct p.plant_id)
from tb_fin_accounts_receivable as a
join tb_core_plant_info as p on a.facility_id=p.facility_id
group by a.account_id

returns the result set - notice how on AR0004407 there are three (3)
distinct plant IDs.

account_id plant_id
AR0001380 06-01212,06-11593
AR0001868 UNKNOWN
AR0004407 06-00923,06-08280,06-10918
AR0005653 06-15029,NEW
AR0008242 06-15696,06-15697
AR0012428 06-13461
AR0016502 06-08117
AR0017908 06-06716
AR0018098 06-03182
AR0018104 06-07057M
AR0018304 06-02050
AR0023644 06-02266
AR0024440 06-15765
AR0028667 EVALUATE,NEW

this function is quite useful when reporting to a bill, etc.

[quoted text, click to view]

Fredrik Wahlgren
3/9/2005 12:01:48 AM

[quoted text, click to view]

I don't quite get it. Can you provide a minimal example which shows a few
entries in the database and the expected result?

7Fredrik

Walter Clayton
3/9/2005 12:33:06 AM

If there were a definable finite number of values to be concatenated, a SQL
specific solution could be written. However if you want a generalized
solution try the following although I've no real way to test it.
A model running against NorthWind did work however.

declare @account_id varchar (10) /* or whatever it may be */
, @delim varchar (1)
, @facility_id varchar(20)
, @plant_id varchar(20)
, @plant_str varchar(200); /* bump up as needed */

declare acct_csr cursor for
select distinct
account_id
, facility_id
from tb_fin_accounts_receivable
order by account_id; /* this part optional */

open acct_csr;

fetch next from acct_csr
into @account_id
, @facility_id;

while @@fetch_status = 0
begin
select @facility_str = ''
, @delim = ''; /* set these to null */
declare plant_csr cursor for
select distinct plant_id
from tb_core_plant_info
where facility_id = @facility_id;
open plant_csr;
while @@fetch_status = 0
begin
fetch next from plant_csr
into @plant_id;
select @plant_str = @plant_str + @delim + @plant_id;
select @delim = ',';
end
close plant_csr
deallocate plant_csr
print @account_id + ' ' + plant_str;
fetch next from acct_csr
into @account_id
, @facility_id
end

close emp_csr;
deallocate emp_csr;

--
Walter Clayton
Any technology distinguishable from magic is insufficiently advanced.

[quoted text, click to view]

John Stender
3/31/2005 1:41:06 PM

Thanks, I'll need to tweak it a bit for my needs, but it will work. Sorry it
took so long to get back to you. It neveer seems to be just one project at a
time.

John

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

sql server new users : "List" Function equivalent