Need replacement for sybase list aggregate

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

Need Help with UDF's Please

I need a scalar function that I can call from a select statement (with other
criteria) that will dynamically return all the columns (And sometimes their
datatypes with a mode option), from a table or view from any database on tha
t
server. The function needs to work in both SQL 2000 and SQL 2005. I have
listed the function I have tried to create below (using the master database
trying to make a system object but 2005 will not allow me to update system
tables with the UID of 4 - sys in 2005 and system_schema_function in
2000...yes I already know I am a bad developer for trying to cheat). Any
help on this would be greatly appreciated!
Please excuse the formating in advance (was created in VS sql 2005).
Thank You
Jared
/ ****************************************
******************/
/* Name : fn_getcolumns
*/
/* Version : 1.0
*/
/* Author : Jared Kirkpatrick
*/
/* Date : 2006-01-01
*/
/* Description : Retrives column information about an object
*/
/ ****************************************
*****************/
/* Date : Version: Who: Description
*/
/ ****************************************
*****************/
/* 2006-01-01 : 1.0 : JSK : Initial Release.
*/
/ ****************************************
*****************/
USE [master];
GO
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'fn_getcolumns')
BEGIN
DROP FUNCTION [dbo].[fn_getcolumns]
END;
GO
CREATE FUNCTION [dbo].[fn_getcolumns]
(
@.p_DB [NVARCHAR](100)
,@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
,@.v_Result [NVARCHAR](4000)
SELECT @.v_SQL =
'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
+ CHAR(13) + CHAR(10) +
' '' ,['' + [Column_Name] + ''] '' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN @.d_Mode = 1 '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
+ CHAR(13) + CHAR(10) +
' ''['' + UPPER([Data_Type]) + '']'' + '
+ CHAR(13) + CHAR(10) +
' CASE WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''CHAR'''
+ CHAR(13) + CHAR(10) +
' ,''VARCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NCHAR'''
+ CHAR(13) + CHAR(10) +
' ,''NVARCHAR'''
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Character_Maximum_Length])
+ '') '' ' + CHAR(13) + CHAR(10) +
' WHEN [Data_Type] IN ( '
+ CHAR(13) + CHAR(10) +
' ''DECIMAL'' '
+ CHAR(13) + CHAR(10) +
' ,''NUMERIC'' '
+ CHAR(13) + CHAR(10) +
' ) '
+ CHAR(13) + CHAR(10) +
' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(''
+ ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
'' + ' + CHAR(13) + CHAR(10) +
' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') '' '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END '
+ CHAR(13) + CHAR(10) +
' ELSE '''' '
+ CHAR(13) + CHAR(10) +
' END + CHAR(13) + CHAR(10) '
+ CHAR(13) + CHAR(10) +
' FROM [INFORMATION_SCHEMA].[COLUMNS] '
+ CHAR(13) + CHAR(10) +
' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
+ CHAR(13) + CHAR(10) +
' AND [Table_Name] = ''' + @.p_Object + ''' '
+ CHAR(13) + CHAR(10) +
' ORDER BY [Ordinal_Position] '
+ CHAR(13) + CHAR(10) +
' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
EXEC sp_executesql @.v_SQL,
N'@.d_Result [NVARCHAR](4000) OUTPUT',
N'@.d_Mode [INT]' ,
@.v_Result OUTPUT ,
@.d_Mode = @.p_Mode
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)Thats an easy one, you can=B4t use execute statements or DML (and
non-temp tables) in functions. Therefore you have to use stored
procedures.
HTH, Jens Suessmeyer.|||The RETURN Statement at the end should be:
RETURN @.v_Result
NOT
RETURN @.v_SQL
Excuse me I was trobleshooting.
"kirk1880" wrote:

> I need a scalar function that I can call from a select statement (with oth
er
> criteria) that will dynamically return all the columns (And sometimes thei
r
> datatypes with a mode option), from a table or view from any database on t
hat
> server. The function needs to work in both SQL 2000 and SQL 2005. I have
> listed the function I have tried to create below (using the master databas
e
> trying to make a system object but 2005 will not allow me to update system
> tables with the UID of 4 - sys in 2005 and system_schema_function in
> 2000...yes I already know I am a bad developer for trying to cheat). Any
> help on this would be greatly appreciated!
> Please excuse the formating in advance (was created in VS sql 2005).
> Thank You
> Jared
> / ****************************************
******************/
> /* Name : fn_getcolumns
> */
> /* Version : 1.0
> */
> /* Author : Jared Kirkpatrick
> */
> /* Date : 2006-01-01
> */
> /* Description : Retrives column information about an object
*/
> / ****************************************
*****************/
> /* Date : Version: Who: Description
> */
> / ****************************************
*****************/
> /* 2006-01-01 : 1.0 : JSK : Initial Release.
> */
> / ****************************************
*****************/
> USE [master];
> GO
> IF EXISTS(SELECT 1
> FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
> WHERE [SPECIFIC_SCHEMA] = 'dbo'
> AND [SPECIFIC_NAME] = 'fn_getcolumns')
> BEGIN
> DROP FUNCTION [dbo].[fn_getcolumns]
> END;
> GO
> CREATE FUNCTION [dbo].[fn_getcolumns]
> (
> @.p_DB [NVARCHAR](100)
> ,@.p_Owner [NVARCHAR](100)
> ,@.p_Object [NVARCHAR](100)
> ,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
> )
> RETURNS [NVARCHAR](4000)
> WITH ENCRYPTION
> AS
> BEGIN -- Begin function
> /* Declare Variables */
> DECLARE @.v_SQL [NVARCHAR](4000)
> ,@.v_Result [NVARCHAR](4000)
> SELECT @.v_SQL =
> 'SELECT @.d_Result = ISNULL(@.d_Result, '''') + '' '' + '
> + CHAR(13) + CHAR(10) +
> ' '' ,['' + [Column_Name] + ''] '' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN @.d_Mode = 1 '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Column_Name]))) + '
> + CHAR(13) + CHAR(10) +
> ' ''['' + UPPER([Data_Type]) + '']'' + '
> + CHAR(13) + CHAR(10) +
> ' CASE WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''CHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''VARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ,''NVARCHAR'''
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Character_Maximum_Length
])
> + '') '' ' + CHAR(13) + CHAR(10) +
> ' WHEN [Data_Type] IN ( '
> + CHAR(13) + CHAR(10) +
> ' ''DECIMAL'' '
> + CHAR(13) + CHAR(10) +
> ' ,''NUMERIC'' '
> + CHAR(13) + CHAR(10) +
> ' ) '
> + CHAR(13) + CHAR(10) +
> ' THEN REPLICATE('' '', (25 - LEN([Data_Type]))) + ''(
''
> + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Precision]) + '',
> '' + ' + CHAR(13) + CHAR(10) +
> ' CONVERT(NVARCHAR(10), [Numeric_Scale]) + '') ''
'
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END '
> + CHAR(13) + CHAR(10) +
> ' ELSE '''' '
> + CHAR(13) + CHAR(10) +
> ' END + CHAR(13) + CHAR(10) '
> + CHAR(13) + CHAR(10) +
> ' FROM [INFORMATION_SCHEMA].[COLUMNS] '
> + CHAR(13) + CHAR(10) +
> ' WHERE [Table_Schema] = ''' + @.p_Owner + ''' '
> + CHAR(13) + CHAR(10) +
> ' AND [Table_Name] = ''' + @.p_Object + ''' '
> + CHAR(13) + CHAR(10) +
> ' ORDER BY [Ordinal_Position] '
> + CHAR(13) + CHAR(10) +
> ' SELECT @.d_Result = STUFF(@.d_Result, 1, 3, '' '') '
> EXEC sp_executesql @.v_SQL,
> N'@.d_Result [NVARCHAR](4000) OUTPUT',
> N'@.d_Mode [INT]' ,
> @.v_Result OUTPUT ,
> @.d_Mode = @.p_Mode
> RETURN @.v_SQL;
> END; -- Begin function
> GO
> --SELECT [dbo].[fn_getcolumns]('pubs', 'dbo', 'authors', 0)|||Jens,
First: I really appreciate the feedback
Second: I understand that you cannot execute statements in UDF's which is
why I am looking for help. A sp will not help me in this situation because
I
need the information within another dynamic statement, and you cannot call a
n
sp within inline SQL. I could use the code that makes this UDF and place it
in every instance I need within the sp I call but we are talking about 87
diffrent calls to date and more coming around the corner. I would really
like to find a solution that will allow me to somehow do the following by
making a system function or I will need to start making an xp that can be
called from a UDF (Of cource that calls for me to register the xp and have
code outside of T-SQL that makes things harder to manage).
"Jens" wrote:

> Thats an easy one, you can′t use execute statements or DML (and
> non-temp tables) in functions. Therefore you have to use stored
> procedures.
> HTH, Jens Suessmeyer.
>|||> why I am looking for help. A sp will not help me in this situation
> because I
> need the information within another dynamic statement, and you cannot call
> an
> sp within inline SQL.
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dynamic_sql.html|||kirk1880 (kirk1880@.discussions.microsoft.com) writes:
> Second: I understand that you cannot execute statements in UDF's which
> is why I am looking for help. A sp will not help me in this situation
> because I need the information within another dynamic statement, and you
> cannot call an sp within inline SQL. I could use the code that makes
> this UDF and place it in every instance I need within the sp I call but
> we are talking about 87 diffrent calls to date and more coming around
> the corner. I would really like to find a solution that will allow me
> to somehow do the following by making a system function or I will need
> to start making an xp that can be called from a UDF (Of cource that
> calls for me to register the xp and have code outside of T-SQL that
> makes things harder to manage).
There is a way: you can call extended stored procedures from UDFs. Thus
you can call xp_cmdshell which in it's turns invokes to OSQL and
runs the query. The query would have to insert the data into a table,
and then you would pick up the data from the table in the UDF.
Now, the design of UDFs is that they are not supposed to change database
state, which is why all those restrictions with them are there. This is
a loophole, and there is not really any guarantees that it will work
as expected - if you get something work now, it could break SQL 2005
or in the next service pack.
Thus, I strongly discourage you from trying this. It's a fun thing to
do at parties, but is unsuitable for production code.
What you really should do is to go back to your original business
problem and analyse why you ended up with all these 87 calls. The best
I can recommend at this point is to bounce data over a temp table, or
a process-keyed table, see http://www.sommarskog.se/share_data.html
for details. But I suspect that you need a complete redesign.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also not preferable would be to use OPENQUERY which is able to execute
a Storedprocedure as a source of a result set, but this has many
mantrap and is not suggestable.
HTH, Jens Suessmeyer.|||Thank you everyone that replied to this post. The solution is actually
simplified (I was trying to make it way to hard). I hold a master schema
table of all the user databases that are will be using dynamic table/view
creates and dynamic insert/update/delete. So my original code/function that
is not dynamic (listed below) works great but only in the database that the
original objects reside...so the solution is to use OSQL and have a system
wrapper that compiles the same function in every database that is flagged,
that way the dynamic procedure that is already in whatever database that is
flagged will have access to return the results I need. Kinda of a kluge to
have multiple objects with diffrent id's in each database, but it works.
IF EXISTS(SELECT 1
FROM [INFORMATION_SCHEMA].[ROUTINES] (NOLOCK)
WHERE [SPECIFIC_SCHEMA] = 'dbo'
AND [SPECIFIC_NAME] = 'FSU_GetColumns')
BEGIN
DROP FUNCTION [dbo].[FSU_GetColumns]
END;
GO
CREATE FUNCTION [dbo].[FSU_GetColumns]
(
@.p_Owner [NVARCHAR](100)
,@.p_Object [NVARCHAR](100)
,@.p_Mode [INT] -- 1 = with datatypes; 0 = without datatypes
)
RETURNS [NVARCHAR](4000)
WITH ENCRYPTION
AS
BEGIN -- Begin function
/* Declare Variables */
DECLARE @.v_SQL [NVARCHAR](4000)
SELECT @.v_SQL = ISNULL(@.v_SQL, '') + ' ' +
' ,[' + [Column_Name] + '] ' +
CASE WHEN @.p_Mode = 1
THEN REPLICATE(' ', (25 - LEN([Column_Name]))) +
'[' + UPPER([Data_Type]) + ']' +
CASE WHEN [Data_Type] IN (
'CHAR'
,'VARCHAR'
,'NCHAR'
,'NVARCHAR'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Character_Maximum_Length]) +
') '
WHEN [Data_Type] IN (
'DECIMAL'
,'NUMERIC'
)
THEN REPLICATE(' ', (25 - LEN([Data_Type]))) + '(' +
CONVERT(NVARCHAR(10), [Numeric_Precision]) + ', ' +
CONVERT(NVARCHAR(10), [Numeric_Scale]) + ') '
ELSE ''
END
ELSE ''
END + CHAR(13) + CHAR(10)
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [Table_Schema] = @.p_Owner
AND [Table_Name] = @.p_Object
ORDER BY [Ordinal_Position]
SELECT @.v_SQL = STUFF(@.v_SQL, 1, 3, ' ');
RETURN @.v_SQL;
END; -- Begin function
GO
--SELECT [dbo].[FSU_GetColumns]('dbo', 'TU_Registry', 1);