表结构如下
pro_tabpro_code org_code
10007020101 101
10007020101 102
10007020102 101
10007020102 102
10007020102 103
23007020102 105
...我想按pro_code来分组结构如下
10007020101 101,102
10007020102 102,102,103
23007020102 105..
org_code以逗号相加
pro_tabpro_code org_code
10007020101 101
10007020101 102
10007020102 101
10007020102 102
10007020102 103
23007020102 105
...我想按pro_code来分组结构如下
10007020101 101,102
10007020102 102,102,103
23007020102 105..
org_code以逗号相加
解决方案 »
- Sqlserver2012 的 IntegrationService 服务无法启动,VIA协议已禁止,也不行,请问是什么原因?
- 求sql语句(很多人都无法解决呀)
- 其他会话正在使用事务的上下文
- sql数据库被注入,很多表被追加了script,怎么快速去除?
- 时间判断的问题?
- 求一个SQL语句
- 请教高手,帮我看看这个触发器的问题
- 求一设计思想或者数据结构!(做过计数器的朋友应该能搞定!) 分数在另外贴里!
- 用ADO远程连接SQL2000,在装有SQL2000客户端的电脑上执行正常,换在其他机器没装sql2000客户端的执行就报错???
- 替换字符串
- SqlServer2005 右键打开表 查看表内数据报错。。。
- sql存储过程
-- Author: T.O.P
-- Create date: 2009/11/30
-- Version: SQL SERVER 2005
-- =============================================
declare @tb1 table([pro_code] bigint,[org_code] int)
insert @tb1
select 10007020101,101 union all
select 10007020101,102 union all
select 10007020102,101 union all
select 10007020102,102 union all
select 10007020102,103 union all
select 23007020102,105select [pro_code], [org_code]=stuff((select ','+CAST([org_code] AS VARCHAR(5)) from @tb1 t where [pro_code]=a.[pro_code] for xml path('')), 1, 1, '')
from @tb1 a
group by [pro_code]
--测试结果:
/*
pro_code org_code
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10007020101 101,102
10007020102 101,102,103
23007020102 105(3 row(s) affected)*/
--*******************************************************************************************
表结构,数据如下:
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc 需要得到结果:
id values
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即:group by id, 求value 的和(字符串相加)1. 旧的解决方法(在sql server 2000中只能用函数解决。)
--=============================================================================
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
--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + value FROM tb WHERE id=@id group by value
RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_strUnite
go
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为2 行)
*/
2005 XML
declare @pro_tab table (pro_code nvarchar(20),
org_code nvarchar(20))
insert into @pro_tab select '10007020101','101'
union all select '10007020101','102'
union all select '10007020102','101'
union all select '10007020102','102'
union all select '10007020102','103'
union all select '23007020102','105'
select * from
(select distinct pro_code from @pro_tab) A cross apply
(select code=stuff(replace(replace((select org_code from @pro_tab as B
where B.pro_code=A.pro_code for xml auto),'<B org_code="',','),'"/>',''),1,1,'')) B/*
(6 行受影响)
pro_code code
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10007020101 101,102
10007020102 101,102,103
23007020102 105(3 行受影响)*/