22,209
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([SO_no] NVARCHAR(10),[amt] INT)
INSERT [tb]
SELECT 'SO001',30 UNION ALL
SELECT 'SO002',50 UNION ALL
SELECT 'SO003',60 UNION ALL
SELECT 'SO001',70 UNION ALL
SELECT 'SO002',100 UNION ALL
SELECT 'SO001',200 UNION ALL
SELECT 'SO002',120 UNION ALL
SELECT 'SO004',null UNION ALL
SELECT 'SO005',300 UNION ALL
SELECT 'SO005',500
GO
--SELECT * FROM [tb]
IF NOT OBJECT_ID('[f_str]') IS NULL
DROP FUNCTION [f_str]
GO
create function dbo.f_str(@SO_no varchar(20),@flag varchar(10))
returns varchar(100)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + @flag , '') + ltrim([amt]) from tb where SO_no = @SO_no
return @str
end
go
select distinct SO_no,dbo.f_str(So_no,',') as Amt from tb
/*
SO_no Amt
SO001 30,70,200
SO002 50,100,120
SO003 60
SO004 NULL
SO005 300,500
*/
select t.* from t_enterprise_member
select t.member_id,t.name,t.sex,
(select replace(wm_concat(DEPT_NAME),',','|') from bsc_enterprise_dept
where DEPT_ID in (select DEPT_ID from bsc_enterprise_dept_member where MEMBER_ID = t.member_id) )
from T_Enterprise_Member t
2000自定義函數
create function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go
--调用函数
select id , value = dbo.f_str(id) from tb group by id
/*
标题:按某字段合并字符串之一(简单合并)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-06
地点:广东深圳
描述:将如下形式的数据按id字段合并value字段。
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果:
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求 value 的和(字符串相加)
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
create function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go
--调用函数
select id , value = dbo.f_str(id) from tb group by id
drop function dbo.f_str
drop table tb
--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by id
drop table tb
--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
if @id = @id_old
select @s = @s + ',' + cast(@value as varchar)
else
begin
insert @t values(@id_old , stuff(@s,1,1,''))
select @s = ',' + cast(@value as varchar) , @id_old = @id
end
fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursor
select * from @t
drop table tb
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-01-04 20:34:15
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([SO_no] NVARCHAR(10),[amt] INT)
INSERT [tb]
SELECT 'SO001',30 UNION ALL
SELECT 'SO002',50 UNION ALL
SELECT 'SO003',60 UNION ALL
SELECT 'SO001',70 UNION ALL
SELECT 'SO002',100 UNION ALL
SELECT 'SO001',200 UNION ALL
SELECT 'SO002',120 UNION ALL
SELECT 'SO004',null UNION ALL
SELECT 'SO005',300 UNION ALL
SELECT 'SO005',500
GO
--SELECT * FROM [tb]
-->SQL查询如下:
select SO_no,amt=STUFF((select ','+ltrim(amt) from tb where so_no=t.so_no for XML path('')),1,1,'')
from tb t
group by so_no
/*
SO_no amt
---------- -----------------
SO001 30,70,200
SO002 50,100,120
SO003 60
SO004 NULL
SO005 300,500
(5 行受影响)
*/
create table A
(
SO_no char(5),
amt int default null,
constraint A_PK primary key (SO_no,amt)
)
go
----插入数据
insert A
select 'SO001','30' union all
select 'SO002','50' union all
select 'SO003','60' union all
select 'SO001','70' union all
select 'SO002','100' union all
select 'SO001','200' union all
select 'SO002','120' union all
select 'SO004','' union all
select 'SO005','300' union all
select 'SO005','500'
go
create function lin
(@no varchar(50))
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+',','')+convert(varchar(10),amt) from A where SO_no=@no
return @sql
end
go
select distinct SO_no,dbo.lin(SO_no) from a
go
drop table a
drop function lin
--创建数据表A
create table A
(
SO_no char(5),
amt int default null,
constraint A_PK primary key (SO_no,amt)
)
go
----插入数据
insert A
select 'SO001','30' union all
select 'SO002','50' union all
select 'SO003','60' union all
select 'SO001','70' union all
select 'SO002','100' union all
select 'SO001','200' union all
select 'SO002','120' union all
select 'SO004','' union all
select 'SO005','300' union all
select 'SO005','500'
go
-----------查询语句
declare @temptable table--创建临时表
(
SO_no char(5),
amt varchar(40)
)
-----------第一部分
--向临时表中插入字段SO_no的所有记录
insert into @temptable(SO_no) select distinct SO_no from A
-----------第二部分
declare @Anum int,@i int,@getAamt char(5),@no char(5)
set @i=1
select @Anum=count(*) from A--获得A表中的记录数
while (@i<=@Anum)
begin
declare @temptable_str varchar(40)
--抽取A表中的一行记录
select top 1 @no=SO_no,@getAamt=amt from A where amt not in (select top (@i-1) amt from A)
--向临时表字段 amt 中插入数据
select @temptable_str=amt from @temptable where SO_no=@no
if(@temptable_str is NULL)
begin
update @temptable set amt=@getAamt where SO_no=@no
end
else
begin
update @temptable set amt=rtrim(@temptable_str)+','+@getAamt where SO_no=@no
end
set @i=@i+1
end
select * from @temptable
go
---------结果---------
--SO_no amt
--SO001 30,70,200
--SO002 50,100,120
--SO003 60
--SO004 0
--SO005 300,500
----------------------
--不简洁,但很实用!献丑啦...