在一行中显示一列的多个值(SQL Oracle) [英] Display multiple values of a column in one row (SQL Oracle)

查看:278
本文介绍了在一行中显示一列的多个值(SQL Oracle)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

可能重复:
是否有Oracle SQL查询将多行汇总为一行?

Possible Duplicate:
Is there an Oracle SQL query that aggregates multiple rows into one row?

我正在尝试创建一个SQL查询,该查询可以从表中返回数据以在一行中显示一列的多个值.

I'm trying to to create a SQL query that can return data from a table to display multiple values of a column in one row.

例如,这是表格设置:

SEQ   ROWSEQNUM   ASSISTING_ASSOCIATES  
100   2           19332816  
100   1           1366344  
103   1           12228238  
104   1           1366474 

我需要查询结果如下:

 SEQ   ROWSEQNUM   ASSISTING_ASSOCIATES  
 100   1           1366344; 19332816  
 103   1           12228238  
 104   1           1366474 

有人有见识吗?

推荐答案

我认为这应该起作用,假设对于每个SEQ值,总会有一行带有ROWSEQNUM=1的行,并且ROWSEQNUM的值按顺序增加没有缝隙.

I think this ought to work, assuming that for each SEQ value there is always a row with ROWSEQNUM=1 and the values for ROWSEQNUM increase sequentially with no gaps.

select seq, min(rowseqnum), max(assoc_list)
from (
  select seq, rowseqnum, sys_connect_by_path(assisting_associate,';') assoc_list
    from assoc_table
    start with rowseqnum=1
    connect by seq = prior seq and rowseqnum = prior rowseqnum + 1
  )
group by seq

这篇关于在一行中显示一列的多个值(SQL Oracle)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆