表A
id name
1 a
2 b
3 c
4 d
5 e表Bid phone aId
1 123 1
2
3 123 1
4 123 1
5 123 2
6 123 3
7 123 2
8 123 2
9 123
10 123 2要求结果:
id name phone
1 a 123,123,
2 b 123,123,123,123,
3 c 123,
4 d
5 e
id name
1 a
2 b
3 c
4 d
5 e表Bid phone aId
1 123 1
2
3 123 1
4 123 1
5 123 2
6 123 3
7 123 2
8 123 2
9 123
10 123 2要求结果:
id name phone
1 a 123,123,
2 b 123,123,123,123,
3 c 123,
4 d
5 e
id name
表B id phone aId --create function f_str(@aid int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
select @s= isnull(@s+',','')+ltirm(phone) from tb where aid = @aid
return @s
end
goselect a.*,dbo.f_str(a.id) as c
from ta a
SumString函数的5各参数的意思分别是:
1. 表名;
2. 你想Group BY的字段名
3. 你想sum的那个字段名
4. Group By字段的值
5. Sum字符串时的分隔符
CREATE OR REPLACE FUNCTION SumString(
I_TableName IN VARCHAR2 ,
I_GroupColName IN VARCHAR2 ,
I_ResultColName IN VARCHAR2 ,
I_GroupColValue IN VARCHAR2 ,
I_Separator IN VARCHAR2
)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR ;
C_Cur T_Cur ;
V_Sql VARCHAR2(2000) ;
V_Result VARCHAR2(2000) ;
V_Tmp VARCHAR2(200) ;
V_Cnt NUMBER := 0 ;
BEGIN
V_Result := '' ;
V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || '''' ;
OPEN C_Cur FOR V_Sql ;
LOOP
FETCH C_Cur INTO V_Tmp ;
EXIT WHEN C_Cur%NOTFOUND ;
IF V_Cnt = 0 THEN
V_Result := V_Tmp ;
ELSE
V_Result := V_Result || I_Separator || V_Tmp ;
END IF ;
V_Cnt := V_Cnt + 1 ;
END LOOP ;
CLOSE C_Cur ;
RETURN V_Result ;
END SUMSTRING; SELECT id, SumString( '表', 'id' , 'name' , 'id' , '-' ) SUM_B FROM 表 GROUP BY id;
标题:合并相同列的数据
作者:爱新觉罗.毓华
时间:2008-05-05
地点:广东深圳
*//*情况说明
NAME USERID
张三 KB001
张三 KB003
李四 KB001
李四 KB002
李四 KB003用SQL实现表示如下:
NAME USERID
张三 KB001,KB003
李四 KB001,KB002,KB003
*/create table tb(NAME varchar2(10) , USERID varchar2(10))
insert into tb values('张三' , 'KB001');
insert into tb values('张三' , 'KB003');
insert into tb values('李四' , 'KB001');
insert into tb values('李四' , 'KB002');
insert into tb values('李四' , 'KB003');--1
select name,rtrim(
max(decode(USERID , 'KB001' , USERID || ',' , '')) ||
max(decode(USERID , 'KB002' , USERID || ',' , '')) ||
max(decode(USERID , 'KB003' , USERID || ',' , '')),',') userid
from tb
group by name--2
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid , ',')) , ',') userid
FROM (SELECT NAME , userid , row_number() over(PARTITION BY NAME ORDER BY userid) rn , rownum prn FROM tb)
START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
GROUP BY NAME
ORDER BY NAME;drop table tb/*
NAME USERID
---------- ---------------------------------
李四 KB001,KB002,KB003
张三 KB001,KB003
2 rows selected
*/
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-28 17:09:47
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(1))
insert [A]
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[phone] int,[aId] int)
insert [B]
select 1,123,1 union all
select 2,null,null union all
select 3,123,1 union all
select 4,123,1 union all
select 5,123,2 union all
select 6,123,3 union all
select 7,123,2 union all
select 8,123,2 union all
select 9,123,null union all
select 10,123,2
--------------开始查询--------------------------
;with f as
(select a.id,a.name,b.phone from a left join b on a.id=b.aid)select id,name, [phone]=stuff((select ','+ltrim([phone]) from f t where id=f.id for xml path('')), 1, 1, '')
from f
group by id,name
----------------结果----------------------------
/*id name phone
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 a 123,123,123
2 b 123,123,123,123
3 c 123
4 d NULL
5 e NULL(5 行受影响)
*/
if object_id('ta')is not null drop table ta
go
CREATE TABLE ta(id int,name varchar(15))
INSERT ta SELECT
1, 'a' union all select
2, 'b' union all select
3, 'c' union all select
4, 'd' union all select
5, 'e' if object_id('tb')is not null drop table tb
go
CREATE TABLE tb(id int,phone varchar(10), aId int)
INSERT tb SELECT
1, '123' , 1 union all select
2, null , null union all select
3, '123', 1 union all select
4, '123', 1 union all select
5, '123', 2 union all select
6, '123', 3 union all select
7, '123', 2 union all select
8, '123', 2 union all select
9, '123', null union all select
10, '123', 2
----------------------------------------------------------
if object_id('f_str')is not null drop function f_str
go
CREATE function f_str(@aid int)
returns varchar(50)
as
begin
declare @s varchar(50)
select @s=isnull(@s+',','')+phone from tb where aid=@aid
return isnull(@s,'')
end
go select distinct a.id, a.name,phone=dbo.f_str(b.aid)
from ta a
left join tb b
on a.id=b.aid
id name phone
----------- --------------- --------------------------------------------------
1 a 123,123,123
2 b 123,123,123,123
3 c 123
4 d
5 e (5 行受影响)
现有表A,结构及数据如下:
Group Name
1 A
1 B
1 C
2 A
2 D
3 A
3 C现要求将相同Group的Name提出来合并到一个字段,输出如下结果:
Group Name
1 ABC
2 AD
3 AC
1、
select "Group",max(sys_connect_by_path("Name") "Name" from
(
select "Group","Name",rn,lead(rn) over(partition by "Group" order by rn) rn1
from (select "Group","Name",row_number() over(order by "Group","Name") rn from a)
)
start with rn1 is null
connect by rn1 = prior rn
group by "Group"
;
2、
select "Group",max(sys_connect_by_path("Name") "Name" result
from (select "Group","Name",
(row_number() over(order by "Group","Name" desc)
+dense_rank() over(order by "Group")) rn,
max("Name") over(partition by "Group") name1
from a)
start with "Name"= name1
connect by rn-1 = prior rn
group by "Group"
;