Sybase has an aggregate function called list that concatenates the
results into a scalar.
sybase eg:
select dept,list(description) from parts group by dept
This query would return one row for each department. Each row would
contain all the description text for that department.
Is there any way to do the same in TSQL without using a cursor? I'd
like to do this in a subquery.
TIAThere is none. There are certain workarounds though; see:
http://groups.google.com/group/micr...3e?dmode=source
In SQL 2005 you can use some complex methods using XML with CROSS APPLY, but
the most reliable way is to use the server to return the tabular resultset
and use a client side programming language to get concatenate/ format the
way you need. In certain situations, you might be able to use the PIVOT
operator as well.
Anith|||Consider 'type' is your department and 'name' is the descriptions then this
should do what you want...
SQL Server 2005 only...
select type,
(
select name + ',' as [text()]
from sys.objects soi
where soi.type = t.type
order by name
for xml path( '' ), root( 'sysobjects' ), type
)
from ( select distinct type from sys.objects ) as t
Will give output like this...
D <sysobjects>DF__spt_value__statu__436BFEE3,</sysobjects>
IT
<sysobjects> queue_messages_1003150619,queue_messages
_1035150733,queue_messag
es_1067150847,</sysobjects>
P <sysobjects> sp_MSrepl_startup,sp_MScleanupmergepubli
sher,</sysobjects>
S
<sysobjects> sysrowsetcolumns,sysrowsets,sysallocunit
s,sysfiles1,syshobtcolum
ns,</sysobjects>
SQ
<sysobjects> QueryNotificationErrorsQueue,EventNotifi
cationErrorsQueue,Servic
eBrokerQueue,</sysobjects>
U
<sysobjects> spt_fallback_db,spt_fallback_dev,spt_fal
lback_usg,spt_monitor,sp
t_values,</sysobjects>
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Darren" <darren@.nospam.com> wrote in message
news:uGD%23BWc%23FHA.2608@.tk2msftngp13.phx.gbl...
> Sybase has an aggregate function called list that concatenates the results
> into a scalar.
> sybase eg:
> select dept,list(description) from parts group by dept
> This query would return one row for each department. Each row would
> contain all the description text for that department.
> Is there any way to do the same in TSQL without using a cursor? I'd like
> to do this in a subquery.
> TIA|||Have you seen the FOR XML extension that will allow you to do this in the
engine without the need of sending more data to the client.
Its one of the examples I now use of when, for scalability and complexity
reduction this should be done in engine rather than client side.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:O8YqVbc%23FHA.3608@.TK2MSFTNGP09.phx.gbl...
> There is none. There are certain workarounds though; see:
> http://groups.google.com/group/micr...3e?dmode=source
> In SQL 2005 you can use some complex methods using XML with CROSS APPLY,
> but the most reliable way is to use the server to return the tabular
> resultset and use a client side programming language to get concatenate/
> format the way you need. In certain situations, you might be able to use
> the PIVOT operator as well.
> --
> Anith
>|||I know, but I am not sure if the OP is using SQL 2005 or not.
Anith
Monday, March 12, 2012
Need replacement for sybase list aggregate
Labels:
aggregate,
concatenates,
database,
dept,
description,
egselect,
function,
group,
microsoft,
mysql,
oracle,
parts,
replacement,
scalar,
server,
sql,
sybase,
theresults
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment