表A
myid qty unit qtysec unitsec
STON 2.3 CTS 2 PCS
STON 2.5 CTS 3 PCS
STON 5 PCS 2.8 CTS
.... ... ... ... ...得出效果是:
myid qty unit qtysec unitsec
STON 2.3 CTS 2 PCS
STON 2.5 CTS 3 PCS
STON 2.8 PCS 5 PCS
.... ... ... ... ...
也就是將UNIT相同的CTS放在同一欄
myid qty unit qtysec unitsec
STON 2.3 CTS 2 PCS
STON 2.5 CTS 3 PCS
STON 5 PCS 2.8 CTS
.... ... ... ... ...得出效果是:
myid qty unit qtysec unitsec
STON 2.3 CTS 2 PCS
STON 2.5 CTS 3 PCS
STON 2.8 PCS 5 PCS
.... ... ... ... ...
也就是將UNIT相同的CTS放在同一欄
解决方案 »
- 为什么bcp "SELECT ID,SUBSTRING(ExternalKey,7,32),NodeID,PM_Product_ID,LB_Product_ID,StyleTypeID FROM ExternalMapping" out "Exter
- 求一个我想应该不复杂的SQL语句,请大家帮忙!谢谢!
- 伙计做的动网论坛邮件地址收集工具,大家看有什么好的建议?
- 如何从sqlserver 的备份文件 仅仅恢复一个表
- 有關鎖的問題 , 請指教!!
- 用什么语句可以查找所有含有某字段的表呢
- 要访问jsp的网站,必须要打端口号吗?比如,我的ip是61.13.123.123,要访问jsp网页,就要打http://61.13.123.123:8080/index.jsp吗?谢!
- 救人一命胜造七级浮屠~ 谢谢大家了!!
- update字段错误
- 请教一个老问题: 在SQL SERVER 70 中, 用什么语句设置行级锁?要求是: 别人不能读, 也不能写。 另外, 该如何释放?
- 字符串分割的问题?
- 如果在Sql的表里删除两条完全相同的数据的其中一条
/*
标题:按某字段合并字符串之一(简单合并)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间: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')
gocreate function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') + cast(value as varchar) from tb where id = @id
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop 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')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop 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_cursorselect * from @t
drop table tb
use tempdb;
/*
create table A
(
myid nvarchar(10) not null,
qty decimal(18,2) not null,
unit nvarchar(10) not null,
qtysec decimal(18,2) not null,
unitsec nvarchar(10) not null
);
insert into A(myid,qty,unit,qtysec,unitsec)
values
('STON',2.3,'CTS',2,'PCS'),
('STON',2.5,'CTS',3,'PCS'),
('STON',5,'PCS',2.8,'CTS');
*/
select
A.myid,
case when A.unit = 'PCS' then qtysec else qty end as [qty],
case when A.unit = 'PCS' then (select unitsec = 'CTS') else A.unit end as [unit],
case when A.unitsec = 'CTS' then qty else qtysec end as [qtysec],
case when A.unitsec = 'CTS' then (select unitsec = 'PCS') else A.unitsec end as [unit]
from A;
myid,
qty = CASE WHEN unit = 'CTS' THEN qty WHEN unitsec = 'CTS' THEN qtysec ELSE qty END,
unit = CASE WHEN unit = 'CTS' THEN unit WHEN unitsec = 'CTS' THEN unitsec ELSE unit END,
qtysec = CASE WHEN unitsec = 'PCS' THEN qtysec WHEN unit = 'PCS' THEN qty ELSE qtysec END,
unitsec = CASE WHEN unitsec = 'PCS' THEN unitsec WHEN unit = 'PCS' THEN unit ELSE unitsec END
FROM tb
表A
myid qty unit qtysec unitsec
STON 2.3 CTS 2 PCS
STON 2.5 CTS 3 PCS
STON 5 PCS 2.8 CTS
.... ... ... ... ...得出效果是:
myid qty unit qtysec unitsec
STON 2.3 CTS 2 PCS
STON 2.5 CTS 3 PCS
STON 2.8 CTS 5 PCS
.... ... ... ... ...