select row_number() over(order by dbo.SQL_Aggregate(col2)), dbo.SQL_Aggregate(col2) from @t
CREATE TABLE tb(col1 int,col2 varchar(10)) INSERT tb SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'c' UNION ALL SELECT 4,'d'GO--合并处理函数 Alter FUNCTION dbo.f_str() RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+' '+col2 FROM tb RETURN(@re) END GO--调用函数 SELECT col1=min(col1),col2=dbo.f_str() FROM tb order by col1 drop table tb
改一下:CREATE TABLE tb(col1 int,col2 varchar(10)) INSERT tb SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'c' UNION ALL SELECT 4,'d'GO--合并处理函数 create FUNCTION dbo.f_str() RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+' '+col2 FROM tb RETURN(@re) END GO--调用函数 SELECT col1=min(col1),col2=dbo.f_str() FROM tb order by col1 drop table tb
1 a
2 b
3 c
4 d
结果:
1 a b c d
dbo.SQL_Aggregate(col2)
from @t
INSERT tb SELECT 1,'a'
UNION ALL SELECT 2,'b'
UNION ALL SELECT 3,'c'
UNION ALL SELECT 4,'d'GO--合并处理函数
Alter FUNCTION dbo.f_str()
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+' '+col2 FROM tb
RETURN(@re)
END
GO--调用函数
SELECT col1=min(col1),col2=dbo.f_str() FROM tb order by col1 drop table tb
INSERT tb SELECT 1,'a'
UNION ALL SELECT 2,'b'
UNION ALL SELECT 3,'c'
UNION ALL SELECT 4,'d'GO--合并处理函数
create FUNCTION dbo.f_str()
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+' '+col2 FROM tb
RETURN(@re)
END
GO--调用函数
SELECT col1=min(col1),col2=dbo.f_str() FROM tb order by col1 drop table tb