Re: Aggregate functions
From: Steve Kass (skass_at_drew.edu)
Date: 06/28/04
- Next message: George Hester: "Stored Procedure Theorem 1"
- Previous message: George Hester: "Re: Stored Procedure from ASP"
- In reply to: Nigel Rivett: "Re: Aggregate functions"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 27 Jun 2004 21:18:16 -0400
Nigel,
The use of variables is documented, but as far as I know, if a select
statement doesn't produce a result set, the only guarantee here is that
@w will be assigned at least one value, and no guarantee that there will
be one assignment for every row in the table, let alone any guarantee
about the order of assignments. The only mention of this I know of is
http://support.microsoft.com/default.aspx?scid=kb;en-us;287515, which
says "The correct behavior for an aggregate concatenation query is
undefined", and while it also says "In order to achieve the expected
results from an aggregate concatenation query, apply any Transact-SQL
function or expression to the columns in the SELECT list rather than in
the ORDER BY clause," I have my doubts whether that sole off-handed
remark in a relatively confusing KB article is a real indication that
Microsoft intends this technique to be reliable.
SK
Nigel Rivett wrote:
>Think it's documented in a white paper somewhere - I've lost the reference now.
>
>Should be
>declare @w varchar(100)
> select @w=coalesce(@w+',','') +t from w where id=@id
> return @w
>
>
>
>"Steve Kass" wrote:
>
>
>
>>Uri,
>>
>> While this works in many situations, it's not supported or documented,
>>and I don't recommend using it in a production environment.
>>
>>SK
>>
>>Uri Dimant wrote:
>>
>>
>>
>>>impafait
>>>You dont have to know a maximum number of items
>>>Look at below soultion works for you
>>>create table w
>>>(
>>>id int,
>>>t varchar(50)
>>>)
>>>
>>>insert into w values (1,'abc')
>>>insert into w values (1,'def')
>>>insert into w values (1,'ghi')
>>>insert into w values (2,'ABC')
>>>insert into w values (2,'DEF')
>>>select * from w
>>>
>>>
>>>create function dbo.fn_my ( @id int)
>>>returns varchar(100)
>>>as
>>>begin
>>>declare @w varchar(100)
>>> set @w=''
>>> select @w=@w+t+',' from w where id=@id
>>> return @w
>>>end
>>>
>>>select id,
>>>dbo.fn_my (dd.id)
>>>from
>>>(
>>>select distinct id from w
>>>)
>>>as dd
>>>
>>>drop function dbo.fn_my
>>>"imparfait" <imparfait@noway.noway> wrote in message
>>>news:%23ZsOuM6WEHA.3640@TK2MSFTNGP11.phx.gbl...
>>>
>>>
>>>
>>>
>>>>Thank you very much for ur precise and detailed answer.
>>>>
>>>>
>>>>"Steve Kass" <skass@drew.edu> a écrit dans le message de
>>>>news:eH6Yxx5WEHA.2520@TK2MSFTNGP12.phx.gbl...
>>>>
>>>>
>>>>
>>>>
>>>>>There is no feature of SQL Server specifically for this requirement,
>>>>>though user-defined aggregates may be available in SQL Server 2005.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
- Next message: George Hester: "Stored Procedure Theorem 1"
- Previous message: George Hester: "Re: Stored Procedure from ASP"
- In reply to: Nigel Rivett: "Re: Aggregate functions"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
