SQL Server 拼接字符串 [SQL Server]
--创建表use tempdb
IF OBJECT_ID('Tab') is not null
DROP TABLE Tab
GO
CREATE TABLE Tab
(
INT
, 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 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
--调用
SELECT Col1,Col2=dbo.F_Str(Col1)
FROM Tab
GROUP BY Col1
--或者
SELECT DISTINCT Col1,Col2=dbo.F_Str(Col1)
FROM Tab
GO
--运行结果
Col1 Col2
1 a,b,c
2 d,e
3 f
页:
[1]