同一列的多行字符串数据用SQL怎么转换成同一列的一行数据？高手HELP!!

[dollyxz]

同一列的多行字符串数据用SQL怎么转换成同一列的一行数据？



比如：我用select department,userName from users从表中查询出如下数据

department | userName

--------------- --------------

it it1

it it2

it it3

ur ur1

ur ur2



我能不能用什么SQL对department进行分组然后变成如下的结果呢？

department | userName

--------------- --------------

it it1_it2_it3

ur ur1_ur2



似乎行列转换中只能对那些采用聚合函数的才能转换，这个东东就不知道在SQL中怎么办？谢谢！

[zjcxc]

2000 的话, 得自己写个聚合函数, 或者用临时表

2005 可以直接拼出来

[青锋-SS]

可以使用函数来解决.

[areswang]

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

合并数据方法总结

**********************************************************************************************/
--示例数据
if object_id('tb')is not null
drop table tb
go
create table tb(col1 varchar(1),col2 int)
insert into tb select 'a',1
union all
select 'a',2
union all
select 'b',1
union all
select 'b',2
union all
select 'b',3
select * from tb

/*
1------------------游标法

*/
--定义一个表变量
declare @t table(col1 varchar(10),col2 varchar(100))
--定义一个按COL1，COL2排序的游标
declare tb cursor for
select col1,col2 from tb order by col1,col2
declare @col1_old varchar(1),@col1 varchar(1),@col2 int,@s varchar(100)
open tb
fetch tb into @col1,@col2
select @col1_old=@col1,@s=''
while @@fetch_status=0
begin
if @col1=@col1_old
select @s=@s+','+cast(@col2 as varchar(10))
else
begin
insert into @t values(@col1_old,stuff(@S,1,1,''))
select @s=','+cast(@col2 as varchar(10)),@col1_old=@col1
end
fetch tb into @col1,@col2

end
insert into @t values(@col1_old,stuff(@S,1,1,''))
close tb
deallocate tb

select * from @t

/*
2--------------------函数合并法

*/
create function dbo.f_str(@col1 varchar(100))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s =''
select @s=@s+','+cast(col2 as varchar(100))
from tb
where col1=@col1
return (stuff(@s,1,1,''))
end
go
-- drop function dbo.f_str

select distinct col1,col2=dbo.f_str(col1) from tb order by col1

/*
3----------------------临时表法

*/
select col1,col2=cast(col2 as varchar(100))into # from tb
order by col1,col2
select * from #
declare @col1 varchar(10),@col2 varchar(100)
UPDATE # SET
@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
@col1=col1,
col2=@col2
SELECT col1,col2=MAX(col2) FROM # GROUP BY col1

select col1,col2=MAX(col2) into tb2 from # GROUP BY col1

[suyiming]

看邹老大 写函数 ^_^

[zjcxc]

-- 以下查询仅知用于 sql 2005 及之后的版本

SELECT
	*
FROM(
	SELECT DISTINCT
		department
	FROM 你的表
)A
	CROSS APPLY(
		SELECT userNames = (
				SELECT
					
 = userName
				FROM 你的表
				WHERE department = A.department
				FOR XML PATH
(''), TYPE
			).value('/', 'nvarchar(max)')
	)B

[areswang]

原来老大也在

[-狙击手-]

[Quote=引用 2 楼 sdhylj 的回复:]
可以使用函数来解决.
[/Quote]

[zjcxc]

如果是 sql 2000 的话, 参考 3 楼的函数

[青锋-SS]

你们行动太快了.

[lgxyz]

这例子太多了
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
	DECLARE @re varchar(100)
	SET @re=''
	SELECT @re=@re+','+CAST(col2 as varchar)
	FROM tb
	WHERE col1=@col1
	RETURN(STUFF(@re,1,1,''))
END
GO


SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
/*
col1       col2
---------- -----------
a          1,2
b          1,2,3
*/

[hery2002]

合并分拆表_整理贴1
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

[青锋-SS]

create function f_add(@department varchar(200))
returns varchar(8000)
as
declare @rtn varchar(8000)
set @rtn=''
select @rtn=@rtn+'_'+username from users where department=@department
set @rtn=left(@rtn,len(@rtn)-1)
return @rtn
go
select department,f_add(userName) as username from users group by department

[zuo_lin]

不清楚,但是可以先group 然后用游标能做出来

[zuo_lin]

不清楚,但是可以先group 然后用游标能做出来

[areswang]

create table users(department varchar(10),userName varchar(10))
go
insert into users select 'it','it1'
insert into users select 'it','it2'
insert into users select 'it','it3'
insert into users select 'ur','ur1'
insert into users select 'ur','ur2'

create function f_hb
(@department varchar(1000))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s =''
select @s=@s+'_'+cast(userName as varchar(100))
from users
where department=@department
return (stuff(@s,1,1,''))
end
go
select distinct department,dbo.f_hb(department) from users order by department
----------------
it it1_it2_it3

ur ur1_ur2