ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 將多行記錄合并成一行

將多行記錄合并成一行

原创 Linux操作系统 作者:keeking 时间:2009-07-23 18:07:16 0 删除 编辑

I know how to do this in T-SQL, you use the FOR XML PATH(‘’) construct like so:

with t  as(
 select 'Charles' parent, 'William' child union
 select 'Charles', 'Harry' union
 select 'Anne', 'Peter' union
 select 'Anne', 'Zara' union
 select 'Andrew', 'Beatrice' union
 select 'Andrew', 'Eugenie' 
)
SELECT parent, STUFF( ( SELECT ','+ child 
                        FROM t a 
                        WHERE b.parent = a.parent 
                        FOR XML PATH('')),1 ,1, '')  children
FROM t b 
GROUP BY parent

which, yes, turned this:

image

into this:

image

Unfortunately I didn’t know how to accomplish it in Oracle however after a bit of searching around I found the answer:

with t  as(
 select 'Charles' parent, 'William' child from dual union
 select 'Charles', 'Harry' from dual union
 select 'Anne', 'Peter' from dual union
 select 'Anne', 'Zara' from dual union
 select 'Andrew', 'Beatrice' from dual union
 select 'Andrew', 'Eugenie' from dual
)
select parent, rtrim(xmlagg(xmlelement(e,child || ','))
                .extract('//text()'),',') childs from t
group by parent

So, now you know. And so will I if I ever need to find this again!

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

最新简单方法


select id, [value] = stuff((select ','+ [value] from tb t where id = tb.id for xml path('')) ,1,1,'')

from tb

group by id



select id, [value] = (select [value] from tb t where id = tb.id for xml auto) 

from tb

group by id

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

CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'


select * from tb


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
            ), '', ''), 1, 1, '')
)N

 

select stuff('abc',2,3,'xyz')
--stuff 函數刪除指定長度的字符,并在指定的起點位置插入另一組字符.
stuff(character_expression,start,length,insert_expression)

 

select * from
(select distinct [姓名]from dbo.Student ) a outer apply
(select [科目]=
(select [科目] from dbo.Student where a.[姓名]=[姓名]
for xml auto)
) b

 

 

select point ,[value] as name
 from t T1
outer apply
(select [value]=stuff(replace(replace((select name from t where  T1.point >= t.point for xml auto ),'',''),1,1,'')
)
T2


Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

  create function m_str (@id int)

returns varchar(8000)

  as

  begin

  declare @str varchar(8000)

  set @str=''

  select @str=@str +','+ value from ta where id=@id

set @str=STUFF(@str,1,1,'')

  return @str

  end

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7899089/viewspace-610060/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 分割函数
请登录后发表评论 登录
全部评论

注册时间:2009-07-23

  • 博文量
    194
  • 访问量
    242186