sql server 拼接字符串

-->创建表
--
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 Col1,t.Col2 FROM Tab t

/********************结果********************

 Col1 Col2
1 1  a
2 1  b
3 1  c

4 2  d
5 2  e

6 3  f  */

/*******************************************/

--合并表:

--SQL2000用函数:

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