将竖着记录转化为一条记录

--使用游标来处理

CREATE TABLE tab(ID  int,Area varchar(10))
INSERT tab SELECT 1,'A'
UNION ALL SELECT  1,'B'
UNION ALL SELECT  1,'C'
UNION ALL SELECT  2,'D'
UNION ALL SELECT  2,'E'
UNION ALL SELECT  3,'F'

GO
declare @i int
declare @ii varchar(100)
set @ii = ''
declare dd cursor for select distinct id from tab
open dd
fetch dd into @i
WHILE @@FETCH_STATUS = 0
BEGIN
   select @ii=@ii+'.'+area from tab where id = @i
   print convert(varchar(10),@i)+ '    ' +stuff(@ii,1,1,'')
   set @ii = ''
   fetch dd into @i
END
CLOSE dd
DEALLOCATE dd

drop table tab

 

--使用函数来处理

--处理的数据
CREATE TABLE tab(ID  int,Area varchar(10))
INSERT tab SELECT 1,'A'
UNION ALL SELECT  1,'B'
UNION ALL SELECT  1,'C'
UNION ALL SELECT  2,'D'
UNION ALL SELECT  2,'E'
UNION ALL SELECT  3,'F'

GO

--合并处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(800)
SET @re=''
SELECT @re=@re+','+Area
FROM tab
WHERE ID=@id
RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT id,Area=dbo.f_str(id) FROM tab GROUP BY id
--删除测试
DROP TABLE tab
DROP FUNCTION f_str