--创建表
use tempdb
IF OBJECT_ID( ' Tab ' ) is not null
DROP TABLE Tab
GO
CREATE TABLE Tab
(
[Col1] INT
,[Col2] nvarchar( 1 )
)
IF OBJECT_ID( ' Tab ' ) is not null
DROP TABLE Tab
GO
CREATE TABLE Tab
(
[Col1] INT
,[Col2] nvarchar( 1 )
)
--生成测试数据
INSERT Tab
SELECT 1 ,N ' a '
UNION all
SELECT 1 ,N ' b '
UNION all
SELECT 1 ,N ' c '
UNION all
SELECT 2 ,N ' d '
UNION all
SELECT 2 ,N ' e '
UNION all
SELECT 3 ,N ' f '
GO
SELECT 1 ,N ' a '
UNION all
SELECT 1 ,N ' b '
UNION all
SELECT 1 ,N ' c '
UNION all
SELECT 2 ,N ' d '
UNION all
SELECT 2 ,N ' e '
UNION all
SELECT 3 ,N ' f '
GO
SELECT Col1,t.Col2 FROM Tab t
/********************结果********************
Col1 Col2
1 a
1 b
1 c
2 d
2 e
3 f
/*******************************************/
--创建函数
代码
IF OBJECT_ID(
'
F_Str
'
)
is
not
null
DROP FUNCTION F_Str
go
CREATE FUNCTION F_Str
(
@Col1 INT
)
RETURNS NVARCHAR( 1000 )
AS
BEGIN
DECLARE @Str NVARCHAR( 1000 )
SELECT @Str = ISNULL(@Str + ' , ' , '' ) + Col2
FROM Tab
WHERE Col1 = @Col1
RETURN @Str
END
GO
DROP FUNCTION F_Str
go
CREATE FUNCTION F_Str
(
@Col1 INT
)
RETURNS NVARCHAR( 1000 )
AS
BEGIN
DECLARE @Str NVARCHAR( 1000 )
SELECT @Str = ISNULL(@Str + ' , ' , '' ) + Col2
FROM Tab
WHERE Col1 = @Col1
RETURN @Str
END
GO
--调用
SELECT Col1,Col2
=
dbo.F_Str(Col1)
FROM Tab
GROUP BY Col1
FROM Tab
GROUP BY Col1
SELECT DISTINCT Col1,Col2
=
dbo.F_Str(Col1)
FROM Tab
GO
FROM Tab
GO
--运行结果
Col1 Col2
1 a,b,c
2 d,e
3 f