A 1
A 2
A 3
B 4
C 5
C 6
D
变成
A 1,2,3
B 4
C 5,6
D 求大侠帮我写个通用函数吧 是ORACLE数据库的
A 2
A 3
B 4
C 5
C 6
D
变成
A 1,2,3
B 4
C 5,6
D 求大侠帮我写个通用函数吧 是ORACLE数据库的
解决方案 »
- 用sql链接服务器链接到orcale的疑惑
- 为什么老提示:ORA-00922: 缺少或无效选项
- 关于查询问题,求SQL
- 脱了个裤子。。。oracle的。。.dup格式的。
- 不好意思,再问一个问题,怎样才能把数据抛出的异常写入一个我指定的文件里?
- 请问如何用sql语言只获得数据库中日期格式为"yyyy/mm/dd"的数据?谢谢
- 关于row_number() over (partition by)的问题
- 有关oracle自动断开连接的问题请教?
- 100分求Oracle9i的存储过程的快速学习方法
- ORACEL安装问题!winxp下!在线等!
- 大家帮忙看看:oracle 授权 权限设置 。错误:表或视图不存在
- 请教高手SQL语句:用select的结果进行update
-- Author : htl258(Tony)
-- Date : 2010-07-15 17:05:32
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([a] [nvarchar](10),[b] [int])
INSERT INTO [tb]
SELECT 'A','1' UNION ALL
SELECT 'A','2' UNION ALL
SELECT 'A','3' UNION ALL
SELECT 'B','4' UNION ALL
SELECT 'C','5' UNION ALL
SELECT 'C','6' UNION ALL
SELECT 'D',NULL--SELECT * FROM [tb]-->SQL查询如下:
SELECT DISTINCT a,
b=STUFF((SELECT ','+ISNULL(LTRIM(b),'') FROM tb WHERE a=t.a FOR XML PATH('')),1,1,'')
FROM tb t
/*
a b
---------- -------------------------
A 1,2,3
B 4
C 5,6
D (4 行受影响)
*/
drop table tb
go
create table tb (name varchar(5), num int)
insert into tb
select 'A', 1 union all
select 'A', 2 union all
select 'A', 3 union all
select 'B', 4 union all
select 'C', 5 union all
select 'C', 6 union all
select 'D', nullselect name, num = isnull(STUFF((select ','+ CAST( num as varchar(5))
from tb
where name=a.name for xml path('')),1,1,''),'')
from tb a
group by name
SELECT ID,
WMSYS.WM_CONCAT(col) "NEW_NAME"
FROM TABLE_NAME TT
GROUP BY ID;
create table #T1 (A varchar(1),B varchar(1),)
insert into #T1
select 'A',1 union all
select 'A',2 union all
select 'A',3 union all
select 'B',4 union all
select 'C',5 union all
select 'C',6 union all
select 'D',nullselect A,stuff((select ','+B from #T1 where A=t.A for xml path('')),1,1,'') B
from #T1 t
Group by AA B
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A 1,2,3
B 4
C 5,6
D NULL(4 行受影响)
SELECT t.id id, MAX(substr(sys_connect_by_path(t.name, '/'), 2)) str
FROM (SELECT id, name, row_number() over(PARTITION BY id ORDER BY name) rn
FROM TABLE_NAME) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;
SQL> create table tb (ID varchar(5), NAME NUMBER(2));
Table created
SQL>
SQL> insert into tb
2 select 'A', 1 FROM dual union all
3 select 'A', 2 FROM dual union all
4 select 'A', 3 FROM dual union all
5 select 'B', 4 FROM dual union all
6 select 'C', 5 FROM dual union all
7 select 'C', 6 FROM dual union all
8 select 'D', null FROM dual ;
7 rows inserted
SQL> SELECT * FROM tb;
ID NAME
----- ----
A 1
A 2
A 3
B 4
C 5
C 6
D
7 rows selected
SQL>
SQL> SELECT ID,NAME FROM
2 (SELECT t.id id, substr(sys_connect_by_path(t.name, '/'), 2) NAME ,CONNECT_BY_ISLEAF e
3 FROM (SELECT id, name, row_number() over(PARTITION BY id ORDER BY name) rn
4 FROM tb) t
5 START WITH rn = 1
6 CONNECT BY rn = PRIOR rn + 1
7 AND id = PRIOR id
8 )
9 WHERE e=1;
ID NAME
----- --------------------------------------------------------------------------------
A 1/2/3
B 4
C 5/6
D
SQL>