-->创建表
--
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