sql server2000 里面如何实现oracle10g的 wmsys.wm_concat 的功能？

oraclemch 2009-09-24 11:30:54
sql server2000 里面如何实现oracle10g的 wmsys.wm_concat 的功能？
...全文
2225 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
super1021love 2012-08-15
  • 打赏
  • 举报
 回复
楼主咋搞定的 、分享出来么、哭...
UCPRACACIA 2011-10-27
  • 打赏
  • 举报
 回复
楼主，你是怎么解决的啊 我现在是要在存储过程中解决啊
woichenhuan 2011-08-12
  • 打赏
  • 举报
 回复
楼主怎么搞定的？
oraclemch 2009-09-24
  • 打赏
  • 举报
 回复
谢谢大家，我搞定了，谢谢！
oraclemch 2009-09-24
  • 打赏
  • 举报
 回复
有没有一句sql可以搞定的。

你们的都用到了tsql变量了啊！
--小F-- 2009-09-24
  • 打赏
  • 举报
 回复 
----乌龟代码---

合并列值 

--*******************************************************************************************

表结构，数据如下： 

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 

    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 行） 

*/ 

--===================================================================================

2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 

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 * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( 

        SELECT [values]= STUFF(REPLACE(REPLACE( 

            ( 

                SELECT value FROM tb N 

                WHERE id = A.id 

                FOR XML AUTO 

            ), ' <N value="', ','), '"/>', ''), 1, 1, '') 

)N 

drop table tb 



/* 

id          values 

----------- ----------- 

1          aa,bb 

2          aaa,bbb,ccc 



(2 行受影响) 

*/ 



--SQL2005中的方法2 

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, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') 

from tb 

group by id 



/* 

id          values 

----------- -------------------- 

1          aa,bb 

2          aaa,bbb,ccc 



(2 row(s) affected) 



*/ 



drop table tb
soft_wsx 2009-09-24
  • 打赏
  • 举报
 回复 
http://dev.yesky.com/394/2429894.shtml
soft_wsx 2009-09-24
  • 打赏
  • 举报
 回复 
if object_id('tb') is not null drop table tb

create table tb(检验员  varchar(10), 打印日期 nvarchar(10),  漏检次数 int)

go

insert tb select '罗邦卫','2009-7-31',2 

insert tb select '梅强','2009-7-31',2 

insert tb select '周林江','2009-7-31',1 

insert tb select '周强','2009-7-31',3 

insert tb select '陈刚','2009-8-1',1 

insert tb select '陈华林','2009-8-1',2 

insert tb select '董加军','2009-8-1',1 

insert tb select '罗邦卫','2009-8-1',2 

insert tb select '方文祥','2009-8-1',2 

insert tb select '周林江','2009-8-1',2 

insert tb select '周强','2009-8-1',1 

insert tb select '陈刚','2009-8-3',1 

insert tb select '陈华林','2009-8-3', 1 

insert tb select '陈杰','2009-8-3',3 

insert tb select '李林峰','2009-8-3',1 

go







declare @sql nvarchar(4000)

set @sql='select 检验员'   --变量必须初始化赋值



select @sql=@sql+

                 N','+quotename(打印日期)+

                 N'=max(case 打印日期 when '+quotename(打印日期,N'''')+  --quotename(打印日期)=[打印日期],打印日期(打印日期,N'''')='2009-8-3'......

                 N' then 漏检次数 else 0 end)'

       from tb group by 打印日期  --按日期分组

print @sql

exec(@sql+N' from tb group by 检验员')

go





检验员	2009-7-31	2009-8-1	2009-8-3

陈刚	0	1	1

陈华林	0	2	1

陈杰	0	0	3

董加军	0	1	0

方文祥	0	2	0

李林峰	0	0	1

罗邦卫	2	2	0

梅强	2	0	0

周林江	1	2	0

周强	3	1	0
动态SQL
oraclemch 2009-09-24
  • 打赏
  • 举报
 回复
说错了，是sqlserver2005
--小F-- 2009-09-24
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 oraclemch 的回复:]
引用 2 楼 soft_wsx 的回复:
行列转换是吧！


恩，是的，在sqlserver里面如何用一句sql搞定呢？
[/Quote]


2000必须要用到函数
--小F-- 2009-09-24
  • 打赏
  • 举报
 回复 
-------合并表-------

/****************************************************************************************************************************************************** 

合并分拆表数据 



整理人：中国风(Roy) 



日期:2008.06.06 

******************************************************************************************************************************************************/ 



--> --> (Roy)生成測試數據 



if not object_id('Tab') is null 

    drop table Tab 

Go 

Create table Tab([Col1] int,[Col2] nvarchar(1)) 

Insert Tab 

select 1,N'a' union all 

select 1,N'b' union all 

select 1,N'c' union all 

select 2,N'd' union all 

select 2,N'e' union all 

select 3,N'f' 

Go 



合并表: 



SQL2000用函数: 



go 

if object_id('F_Str') is not null 

    drop function F_Str 

go 

create function F_Str(@Col1 int) 

returns nvarchar(100) 

as 

begin 

    declare @S nvarchar(100) 

    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 

    return @S 

end 

go 

Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab 



go 



SQL2005用XML: 



方法1: 



select 

    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') 

from 

    (select distinct COl1 from Tab) a 

Cross apply 

    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 



方法2: 



select 

    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) 

from 

    (select distinct COl1 from Tab) a 

cross apply 

    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE) 

                .query(' <Tab> 

                {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} 

                {concat("",string(/Tab[last()]/@COl2))} 

                </Tab>') 

                )b 



SQL05用CTE: 



;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) 

,Roy2 as 

(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 

union all 

select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) 

select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 





生成结果: 

/* 

Col1        COl2 

----------- ------------ 

1          a,b,c 

2          d,e 

3          f 



(3 行受影响) 

*/
oraclemch 2009-09-24
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 soft_wsx 的回复:]
行列转换是吧！
[/Quote]

恩，是的，在sqlserver里面如何用一句sql搞定呢？
soft_wsx 2009-09-24
  • 打赏
  • 举报
 回复
行列转换是吧！
soft_wsx 2009-09-24
  • 打赏
  • 举报
 回复
wmsys.wm_concat 这个功能是干什么用的！
ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案
ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案
Oracle 11g 重建WMSYS.WM_CONCAT函数
Oracle10g之后有些版本已不包含WMSYS.WM_CONCAT函数，若用到此函数， 系统会提示异常：ORA-00904: "WM_CONCAT": invalid identifier 因此需单独重建此函数，方法如下： 解压附件，在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可, sqlplus / as sysdba SQL>@owmctab.plb; SQL>@owmaggrs.plb; SQL>@owmaggrb.plb;
Oracle重建WMSYS用户及WMSYS.WM_CONCAT函数
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。 简单来说，用PL/SQL执行下一下几个脚本就可以了。 特别要注意：PL/SQL登录时，要选择以sysdba的方式登录。同时用户要有dba的权限（如通过system用户给新用户分配权限：grant connect, resource, dba to scott） 语法：@+文件路径 SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmctab.plb; SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmaggrs.plb SQL>@$ORACLE_HOME\RDBMS\ADMIN\owmaggrb.plb
oracle行转列聚合函数WMSYS.WM_CONCAT
行转列函数，对于学习sql查询的朋友们，可能会遇到这样的要求，此文档中做了详细记载，希望可以帮到你！
ORA-06512 数字或值错误，字符缓冲区太小
ORA-06512 数字或值错误，字符缓冲区太小的解决方法。 有图有真相，希望对你有帮助。
应用实例

27,580

社区成员

68,556

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章