关于一行转一列

;with cte as
(
select '1' id , cast('2012-6-18 10:00' as datetime)  gpstime union all
select '2',cast('2012-6-18 10:30' as datetime) union all
select '3',cast('2012-6-19 11:40' as datetime) union all
select '5',cast('2012-6-19 12:00' as datetime)
)
select 
stuff((
select ';'+id from cte for xml path('')
),1,1,'') resuilt

 

resuilt
----------------------------------------------------------------------------------------------------------------------

1;2;3;5

(1 行受影响)

 

上面这个是用的最普通的方法，近日看到一个 SQL2005以上特有的方法，

DECLARE @DepartmentName VARCHAR(1000)
;with cte as
(
select '1' id , cast('2012-6-18 10:00' as datetime)  gpstime union all
select '2',cast('2012-6-18 10:30' as datetime) union all
select '3',cast('2012-6-19 11:40' as datetime) union all
select '5',cast('2012-6-19 12:00' as datetime)
)
SELECT @DepartmentName = COALESCE(@DepartmentName+';','') + id  FROM cte
select @DepartmentName resuilt

 

结果和上面的一样，实际使用时应该会方便一点，但需要定义一个初始变量，有限制

 

具体的用哪种方法，还要看实际应用