I am implementing user-defined aggregates.  One of which is the string
concatenation (which I think everyone is doing).

The problem I have with this is that you have to hard-code what your
delimter is inside the aggregate code.

I would like my aggregate to allow the delimiter to be set through a second
parameter or something.

Example:
SELECT dbo.DelimitedString(<dbfield>, <delimieter>)
FROM dbo.TableName
GROUP BY
<group by fields,...>

That returns <fieldvalue1><delimiter><fieldvalue2><delimiter><fieldvalue3>

I already know that you can't pass 2 paramters through the Accumulate
function, but does anyone know of another way to pass in a second parameter
or set a property to accomplish what I am trying to do?

JediMaster wrote:
Show quoteHide quoteYou can use the ADO GetString method to do this client side. A serious
drawback of your proposed solution is that you cannot specify an
ordered aggregation so the resulting will always be unpredictable. In
fact there's a much better solution in SQL:

CREATE TABLE T1 (x VARCHAR(10) NOT NULL PRIMARY KEY);

INSERT INTO T1 (x) VALUES ('A');
INSERT INTO T1 (x) VALUES ('B');
INSERT INTO T1 (x) VALUES ('C');

SELECT LEFT(x,LEN(x)-1)
FROM
(SELECT REPLACE( REPLACE(
  (SELECT x
    FROM T1
    ORDER BY x
   FOR XML PATH (''))
   , '<x>', ''),'</x>',',') AS x) AS T ;

Perhaps you should mention the fact that this may only work in SQL 2005.


ML

---
http://milambda.blogspot.com/

Since the subject of this thread specifies, 'user-defined aggregate', it's
safe to assume that the original poster is using SQL 2005.  Also, the
example SELECT statement in the original post will only parse on SQL 2005.

Show quoteHide quote

Khm... Are you sure? I don't see any distinct elements in there that would
not be parsed in SQL 2000. Or am I missing something?


ML

---
http://milambda.blogspot.com/

No, you are right about the syntax check.  I was looking at David's SELECT
statement code.

Show quoteHide quote

Good. For a moment I thought we were back under communist rule and posts were
filtered to prevent incursion of imperialistic ideology. ;)


ML

---
http://milambda.blogspot.com/

Thank you.  I forgot about being able to use the for xml path.  This works
great for the delimitted string, but how about nth percentile.

So, I have a set of number data in a group (based on other grouping
conditions)
(12,12,14,15,15,16,18,20,100,150)

I want to return the value at the 30% percentile which would give me 14.  I
would like the percentile number to be changeable by the user.

Why would you want to mangle data this way only to use it as an ordered set
later?

No method in SQL can guarantee ordering when values are concatenated into a
delimited string.


ML

---
http://milambda.blogspot.com/

In the case of the nth percentile, it is not a delimited string I am trying
to parse.  Lets take salary data for an example.  Based on different
positions in the market, the 50% of the salary range is different.

For an example, I want to return a query that gives me the 50% for each
position in my company.  If I can accumulate market data for each positions
that the market is paying outside my company, I can analyze the data and
determine what I need to pay.

So a query such as:
SELECT
   PositionTitle,
   dbo.NthPercentile(Salary, .30)
FROM SalaryMarketData

would return the following:

Senior Data Architect    $xx
Accountant                   $xx
HR Director                  $xx

This tells me the maximum I want to pay out for positions to hire.



Show quoteHide quote

Ah, ok. :) Thought for a second there you were trying to complicate your life
beyond belief. Sorry.

Look at Razvan's post.


ML

---
http://milambda.blogspot.com/

Hi, JediMaster

Try something like this:

CREATE TABLE T2 (X int)

INSERT INTO T2 VALUES (12)
INSERT INTO T2 VALUES (12)
INSERT INTO T2 VALUES (14)
INSERT INTO T2 VALUES (15)
INSERT INTO T2 VALUES (15)
INSERT INTO T2 VALUES (16)
INSERT INTO T2 VALUES (18)
INSERT INTO T2 VALUES (20)
INSERT INTO T2 VALUES (100)
INSERT INTO T2 VALUES (150)

DECLARE @P tinyint
SET @P=30

SELECT TOP 1 X FROM (
    SELECT TOP (@P) PERCENT X FROM T2 ORDER BY X
) A ORDER BY X DESC

Razvan

JediMaster wrote:

I don't see what this has to do with concatenating a string. Why would
you want to represent values that way? Please include DDL and sample
data (INSERTs) so that we have a clearer spec to work and test with.
See if the following example helps:

CREATE TABLE T1 (x INTEGER NOT NULL /* PRIMARY KEY not specified! */);

INSERT INTO T1 (x)
SELECT 12 UNION ALL
SELECT 12 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 18 UNION ALL
SELECT 20 UNION ALL
SELECT 100 UNION ALL
SELECT 150 ;

DECLARE @ntile FLOAT ;
SET @ntile = 30.00/100.00 ;

WITH T
AS
(SELECT x, RANK() OVER (ORDER BY x) AS  rnk
  FROM T1)
SELECT *
  FROM T
  WHERE rnk = @ntile*(SELECT COUNT(*) FROM T1) ;

Hi, David

We should also take care of the XML escape sequences for various
characters:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    LEFT(x,LEN(x)-1),
    '&apos;',''''),'&lt;','<'),'&gt;','>'),'&quot;','"'),'&amp;','&')
FROM
(SELECT REPLACE( REPLACE(
  (SELECT x
    FROM T1
    ORDER BY x
   FOR XML PATH (''))
   , '<x>', ''),'</x>',',') AS x) AS T ;

Razvan

If you're thinking of using T-SQL, take a look at this example:
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html


ML

---
http://milambda.blogspot.com/

You don't need to use CLR for this, there is an extension to the FOR XML
syntax to allow very fast and scalable concatenation.

Also, the problem with using CLR aggregation is that you cannot control the
order in which data is concatentated, using FOR XML you can...

Example

select type,
       ( select name + ',' as [text()]
         from sys.objects s2
         where s2.type = s1.type
         order by name
         for xml path( '')
        ) as string
from sys.objects s1
group by type

Gives contacenated output, one row per group'd type and a ',' as the
delimiter.

D  DF__spt_value__statu__436BFEE3,
IT
queue_messages_1003150619,queue_messages_1035150733,queue_messages_1067150847,
P  sp_MScleanupmergepublisher,sp_MSrepl_startup,sp_pivot,
S
sysallocunits,sysasymkeys,sysbinobjs,sysbinsubobjs,syscerts,syschildinsts,sysclsobjs,syscolpars,sysconvgroup,sysdbfiles,sysdbreg,sysdercv,sysdesend,sysendpts,sysfiles1,sysftinds,sysguidrefs,syshobtcolumns,syshobts,sysidxstats,sysiscols,syslnklgns,syslogshippers,sysmultiobjrefs,sysnsobjs,sysobjkeycrypts,sysobjvalues,sysowners,sysprivs,sysqnames,sysremsvcbinds,sysrmtlgns,sysrowsetcolumns,sysrowsetrefs,sysrowsets,sysrts,sysscalartypes,sysschobjs,sysserefs,syssingleobjrefs,syssqlguides,systypedsubobjs,sysusermsgs,syswebmethods,sysxlgns,sysxmitqueue,sysxmlcomponent,sysxmlfacet,sysxmlplacement,sysxprops,sysxsrvs,
SQ
EventNotificationErrorsQueue,QueryNotificationErrorsQueue,ServiceBrokerQueue,
U
MSreplication_options,spt_fallback_db,spt_fallback_dev,spt_fallback_usg,spt_monitor,spt_values,

Show quoteHide quote

Is order guaranteed for the FOR-XML method? By definition the order in a
serialized XML is not quaranteed (AFAIK).


ML

---
http://milambda.blogspot.com/

yes, the order is guarenteed.

Tony

Show quoteHide quote

Custom number format
WHERE IN - Return NULL values
GetDate() in UDF
Popuate field with value from last row
Why query optimizer does not select the index?