表1
Col_Name A B C
a1 b1 c1
a1 b2 c1
a1 b3 c1
a2 b4 c2
a2 b5 c2怎样得到如下数据:
Col_Name A B C
a1 b1,b2,b3 c1
a2 b4,b5 a2
不用开发语言只能用SQL语句
Col_Name A B C
a1 b1 c1
a1 b2 c1
a1 b3 c1
a2 b4 c2
a2 b5 c2怎样得到如下数据:
Col_Name A B C
a1 b1,b2,b3 c1
a2 b4,b5 a2
不用开发语言只能用SQL语句
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+人员 from 表 where 部门 = @department
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go--输出结果
/*
部门 人员
---- --------------
1 张三,李四,王五
2 赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go
create table tb(A varchar(100),B varchar(100),C varchar(100))
go
insert into tb select 'a1','b1','c1'
insert into tb select 'a1','b2','c1'
insert into tb select 'a1','b3','c1'
insert into tb select 'a2','b4','c2'
insert into tb
select 'a2','b5','c2'
go
--写一个聚合函数:
create function dbo.fn_Merge(@A varchar(100))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+B from tb where A=@A
return stuff(@r,1,1,'')
end
go-- 调用函数
select A, dbo.fn_Merge(A) as B,C from tb group by A,C
go
drop table tb
drop function fn_Merge
if exists (select 1 from sys.objects where object_id=object_id('表1'))
begin
drop table 表1
end
create table 表1(编号 varchar(10),wt1 varchar(10),wt2 varchar(10))
insert into 表1
select '001','aaa','cccc'
union all select '001','bbb','ffff'
union all select '001','ccc','eeee'
union all select '002','ssss','yyyyy'
go
SELECT *
FROM(
SELECT DISTINCT
编号
FROM 表1
)A
OUTER APPLY(
SELECT
[values]=STUFF(REPLACE(REPLACE(
(
SELECT wt1 FROM 表1 N
WHERE 编号= A.编号
FOR XML AUTO
), '<N wt1="', ','), '"/>', ''), 1, 1, '')
)N
OUTER APPLY(
SELECT
[values]=STUFF(REPLACE(REPLACE(
(
SELECT wt2 FROM 表1 Na
WHERE 编号= A.编号
FOR XML AUTO
), '<Na wt2="', ','), '"/>', ''), 1, 1, '')
)Na
drop table 表1--结果
/**//*
编号 values values
----------- --------------- -------------------
001 aaa,bbb,ccc cccc,ffff,eeee
002 ssss yyyyy
*/
create table tb(A varchar(100),B varchar(100),C varchar(100))
go
insert into tb select 'a1','b1','c1'
insert into tb select 'a1','b2','c1'
insert into tb select 'a1','b3','c1'
insert into tb select 'a2','b4','c2'
insert into tb
select 'a2','b5','c2'
go
--写一个聚合函数:
create function dbo.fn_Merge(@A varchar(100))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+B from tb where A=@A
return stuff(@r,1,1,'')
end
go-- 调用函数
select A, dbo.fn_Merge(A) as B,C from tb group by A,C
go
drop table tb
drop function fn_Merge
/*--结果A B C
a1 b1,b2,b3 c1
a2 b4,b5 a2*/
drop table tbTest
if object_id('fnMerge') is not null
drop function fnMerge
GO
create table tbTest(A varchar(10),B varchar(10),C varchar(10))
insert tbTest
select 'a1', 'b1', 'c1' union all
select 'a1', 'b2', 'c1' union all
select 'a1', 'b3', 'c1' union all
select 'a2', 'b4', 'c2' union all
select 'a2', 'b5', 'c2'
GO
create function fnMerge(@a varchar(10),@c varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + B from tbTest where A = @a and C = @C
return stuff(@str,1,1,'')
end
GO
----查询
select A,dbo.fnMerge(A,C) as B,C
from tbTest group by A,Cdrop table tbTest
drop function fnMerge/*结果
A B C
---------- -----------------
a1 b1,b2,b3 c1
a2 b4,b5 c2
*/
create table tb(A varchar(100),B varchar(100),C varchar(100))
go
insert into tb select 'a1','b1','c1'
insert into tb select 'a1','b2','c1'
insert into tb select 'a1','b3','c1'
insert into tb select 'a2','b4','c2'
insert into tb
select 'a2','b5','c2'
go
--写一个聚合函数:
create function dbo.fn_Merge(@A varchar(100),@C varchar(100))
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+B from tb where A=@A and C=@C
return stuff(@r,1,1,'')
end
go-- 调用函数
select A, dbo.fn_Merge(A,C) as B,C from tb group by A,C
go
drop table tb
drop function fn_Merge
/*--结果A B C
a1 b1,b2,b3 c1
a2 b4,b5 a2*/
(
@col_name varchar(10) --学号
)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=@str+','+b from 表1 where col_name=@col_name
return stuff(@str,1,1,'')
end
go
--
select a1,dbo.f(a),c1 表1 from a,c
-- sql 2005
SELECT
A,
B = REPLACE(STUFF(B, 1, 1, ''), ' ,', ','),
C
FROM(
SELECT DISTINCT
A, C
FROM 表1
)A
CROSS APPLY(
SELECT B =(
SELECT
B
FROM 表1 r
WHERE A = A.A
AND B = A.B
FOR XML AUTO, TYPE
).query('<r>
{for $i in /r/@B return(concat(",",string($i)))}
</r>').value('(/r)[1]', 'nvarchar(max)')
)B
Table 1 Column_Name A B C
a b c
a b1 c1
a1 b2 c2
a1 b3 c3
新表: Column_Name A B
a b,c,b1,c1
a1 b2,c2,b3,c3
怎么得到新表的数据?
INSERT INTO 新表 select A,dbo.fnMerge(A,C) from tbTest group by A,C如果新表不存在,则:
select A,dbo.fnMerge(A,C) as B INTO 新表 from tbTest group by A,C