for SQL Server 2000 or 7.0, I wrote a simple function (tested) -->

digitworm(digitworm)

--table to list
CREATE TABLE t1(id int, v varchar(20))
GO

INSERT t1
SELECT 1, 'A'
UNION
SELECT 1, 'B'
UNION
SELECT 1, 'C'
UNION
SELECT 2, 'A'
UNION
SELECT 2, 'C'
UNION
SELECT 3, 'X'
UNION
SELECT 3, 'Y'
UNION
SELECT 3, 'Z'
GO

CREATE FUNCTION typeList(@id int)
RETURNS varchar(20)
AS
BEGIN
DECLARE @vl varchar(20)
SELECT @vl=''

SELECT @vl = @vl + v + ','
FROM t1
WHERE id = @id

IF ( LEN(@vl) > 0 )
SET @vl = LEFT(@vl, LEN(@vl) - 1 )

RETURN (@vl)
END
GO

CREATE VIEW t1_view
AS
SELECT id, dbo.typelist(id) as type_list
FROM t1
GROUP BY id
GO

SELECT * FROM t1_view
GO

DROP VIEW t1_view
DROP FUNCTION typelist
DROP TABLE t1
GO

(#3310597@0)
Last Updated: 2006-11-9
This post has been archived. It cannot be replied.

1）这个ID明显不是unique的，估计是combination primary key。2）如果是一个view，我想用cursor最简单明了。 -luoboyang(萝卜秧); 2006-11-8 (#3309604@0)

是combination primary key。id unique不是指在这个表里unique。您的答案是？ -bdbs(不多不少); 2006-11-8 (#3309609@0)

现在在脑子里的是：大致是建立一个cursor，然后foreach中，for one ID，用一个变量循环累加type name，完成一个后，insert到一个临时表。然后在view的最后,select * from temptable。 -luoboyang(萝卜秧); 2006-11-8 (#3309621@0)

我的做法： -canadiantire(轮胎-pax et lux); 2006-11-8 {123} (#3309632@0)

1, UDF: SELECT @t = ISNULL( @t, '' ) + ',' + type FROM table WHERE id=@id

2, SELECT udf( ID) FROM table GROUP BY id

嗯，不错。：）// 加个id在2）里面，就是LZ想要得了。：）） -luoboyang(萝卜秧); 2006-11-8 (#3309658@0)

呵呵，这还不是我想要的。因为作为VIEW，在跟其它TABLE或VIEW JOIN的时候，UDF作为一个sub query，performance及其糟糕。比方说假如有1000个unique id，这个UDF就要被CALL1000次。我想知道有没有更好的解。 -bdbs(不多不少); 2006-11-8 {93} (#3309694@0)
顺便说一下，轮胎的UDF里，应该用COALESCE(@t + ',', '') + type，这样可以避免leading comma。

我想标准的SQL是做不了的，不同的数据库应该有不同的方案。ORACLE里面也要用到自定义函数，虽然这函数不需要访问表。写起来有一大堆。 -newkid(newkid); 2006-11-8 (#3309717@0)

Oracle solution (函数来自asktom) -newkid(newkid); 2006-11-8 {2194} (#3309731@0)
本文发表在 rolia.net 枫下论坛create or replace type string_agg_type as object
(
total varchar2(4000),

static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,

member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,

member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/

create or replace type body string_agg_type
is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;

end;
/

CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

CREATE TABLE test (ID NUMBER,TYPE CHAR(1));

INSERT INTO test VALUES(1,'A');
INSERT INTO test VALUES(1,'C');
INSERT INTO test VALUES(2,'B');
INSERT INTO test VALUES(2,'C');
INSERT INTO test VALUES(2,'D');
INSERT INTO test VALUES(3,'A');
INSERT INTO test VALUES(3,'E');

SELECT id,stragg(type) FROM test GROUP BY id;更多精彩文章及讨论，请光临枫下论坛 rolia.net

很Cool, 不懂Oracle，看上去好像是Aggregation 的时候，Ｏｒａｃｌｅ会调用这么个Ｈｏｏｋ似的东西，所以就不必另外查表了？ -canadiantire(轮胎-pax et lux); 2006-11-8 (#3309757@0)

我猜也是这样，这函数似乎是很底层的东西。 -newkid(newkid); 2006-11-8 (#3309768@0)

嗯，看来确实如此。这篇文章说的就是如何用Ｃ＃写一段User Defined Aggregate Function，应该符合搂住的意思 -canadiantire(轮胎-pax et lux); 2006-11-8 (#3309782@0)

狗了一下，发现有这么一段* Extensibility hook to aggregate values over a group during query processing似乎SQL 2005也能做类似的事情。 -canadiantire(轮胎-pax et lux); 2006-11-8 (#3309764@0)

for SQL Server 2005, ---> -digitworm(digitworm); 2006-11-8 {169} (#3309775@0)

SELECT DISTINCT
a.id,
(
SELECT LTrim(RTrim( type )) + ', ' AS [text()]
FROM #t1 b
WHERE b.id = a.id
ORDER BY id, type FROM XML PATH('')
) AS lst
FROM #t1 a

这是Ｈａｃｋ！　：） -canadiantire(轮胎-pax et lux); 2006-11-8 (#3309787@0)
for SQL Server 2000 or 7.0, I wrote a simple function (tested) --> -digitworm(digitworm); 2006-11-9 {733} (#3310597@0)
--table to list
CREATE TABLE t1(id int, v varchar(20))
GO

INSERT t1
SELECT 1, 'A'
UNION
SELECT 1, 'B'
UNION
SELECT 1, 'C'
UNION
SELECT 2, 'A'
UNION
SELECT 2, 'C'
UNION
SELECT 3, 'X'
UNION
SELECT 3, 'Y'
UNION
SELECT 3, 'Z'
GO

CREATE FUNCTION typeList(@id int)
RETURNS varchar(20)
AS
BEGIN
DECLARE @vl varchar(20)
SELECT @vl=''

SELECT @vl = @vl + v + ','
FROM t1
WHERE id = @id

IF ( LEN(@vl) > 0 )
SET @vl = LEFT(@vl, LEN(@vl) - 1 )

RETURN (@vl)
END
GO

CREATE VIEW t1_view
AS
SELECT id, dbo.typelist(id) as type_list
FROM t1
GROUP BY id
GO

SELECT * FROM t1_view
GO

DROP VIEW t1_view
DROP FUNCTION typelist
DROP TABLE t1
GO

不会用view ，用两个cursor做 .实现功能。 -hard20(hard20); 2006-11-8 {990} (#3310118@0)

SET NOCOUNT ON
DECLARE @i_ID int
DECLARE @s_type varchar(2)
DECLARE @str_type varchar(1000)

DECLARE @tbl1 table
(id int,
str_type varchar(1000) )

Declare cur_id cursor
FOR
SELECT ID FROM test1
GROUP BY ID
ORDER BY ID

OPEN cur_id
FETCH NEXT FROM cur_id INTO @i_ID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @s_type = ''
SET @str_type = ''

DECLARE cur_type cursor
FOR SELECT [TYPE] FROM TEST1
WHERE ID = @i_ID

OPEN cur_type
FETCH NEXT FROM cur_type INTO @s_type

WHILE @@FETCH_STATUS = 0

BEGIN
SET @s_type = @s_type + ','
SET @str_type = @str_type + @s_type

FETCH NEXT FROM cur_type INTO @s_type
END

CLOSE cur_type
DEALLOCATE cur_type

SET @str_type = '''' + LEFT (@str_type, LEN(RTRIM(@str_type))- 1) + ''''

INSERT INTO @tbl1
Values (@i_ID,@str_type)

FETCH NEXT FROM cur_id INTO @i_ID
END

CLOSE cur_id
DEALLOCATE cur_id

SELECT * FROM @tbl1

SET NOCOUNT OFF

failed! :( -bdbs(不多不少); 2006-11-8 (#3310224@0)

failed ? 虽然代码比function的长，但效率不一定比那个差。 -hard20(hard20); 2006-11-8 (#3310280@0)

题目要求做个view呀。：） -bdbs(不多不少); 2006-11-9 (#3310538@0)

SQL 2005 ，用PIVOT做，简单又方便，外面再套一层就行了。 -hard20(hard20); 2006-11-8 {226} (#3310120@0)

SELECT ID, [A] AS A, [B] AS B , C AS [C] , D AS [D] , E AS [E],
F AS [F] , J AS [J] ,K AS [K] ,L AS [L]
FROM
(SELECT id,[type]
FROM test1) t1
PIVOT
(
MAX([type])
FOR [type] IN
( A, B, C, D, E ,F, J, K, L)
) AS pvt;

select ID, '('+ListAGG(type)+)' from table1 group by ID -super__7(中奖大家有份); 2006-11-8 (#3310150@0)

good! 简单! 不知道sql server 2005以前的版本有没有这个listagg? -poohbear(毛毛熊); 2006-11-8 (#3310166@0)

现在也没有呀。这只是一个mockup function而已。楼上那是放狗搜出来的吧。好像有点闹笑话了。呵呵。 -bdbs(不多不少); 2006-11-8 (#3310214@0)

Msg 195, Level 15, State 10, Line 1 'ListAGG' is not a recognized built-in function name. --sqlserver 2005 -hard20(hard20); 2006-11-8 (#3310176@0)

奇怪... 我没有sql server 2005, 没办法验证... -poohbear(毛毛熊); 2006-11-8 (#3310181@0)

我有。验证不过 Msg 195, Level 15, State 10, Line 2 'LISTAGG' is not a recognized built-in function name. -hard20(hard20); 2006-11-8 (#3310190@0)
Although this aggregate is NOT built into SQL Server 2005.... -hard20(hard20); 2006-11-8 (#3310195@0)
From you link, it says -bdbs(不多不少); 2006-11-8 {568} (#3310221@0)
Such an aggregate might be used on this table in order to produce a list of strings:
```
SELECT LISTAGG(String)
FROM Strings

Output:

'A, B, C'
```
Although this aggregate is not built into SQL Server 2005, the new system introduces a way of easily achieving this functionality. The most obvious way might be to use the new CLR user-defined aggregates (UDAs). Unfortunately, UDAs have an 8000-byte limit, which severely limits their use when aggregating large sets.

o, 我只看了code... google listagg有三页结果... 原来是未来世界的东西... anyhow, 跟我没有关系, 我没有.... -poohbear(毛毛熊); 2006-11-8 (#3310247@0)

如果你有幸用MySQL, 很简单CREATE VIEW TABLE1VIEW AS select id, GROUP_CONCAT(type) from table1 group by id; -looi500(looi); 2006-11-8 (#3310469@0)

爽 -helloyou(你好！); 2006-11-8 (#3310484@0)
good! 这是oracle的, 估计sql server 2005以下也得按这个思路做 -poohbear(毛毛熊); 2006-11-8 (#3310499@0)
MySQL挺不错的，缺陷是对语法要求很低，对SQL不很熟的人很容易因为错误的语法得到莫名其妙的结果。可惜大公司还没用MySQL作数据库平台的。：（ -bdbs(不多不少); 2006-11-9 (#3310859@0)

@Florida

for SQL Server 2000 or 7.0, I wrote a simple function (tested) -->

Replies, comments and Discussions:

More Topics