ORACLE行合并问题

[qian119110]

如果数据库存储的数据格式像这样
CODE NAME1 NAME2 NAME3
1 A1 NULL NULL
1 A11 NULL NULL
2 NULL A2 NULL
3 NULL NULL A3

我想把CODE2 CODE3的内容合并到CODE1 的第一行
变成
NAME1 NAME2 NAME3
A1 A2 A3
A11 NULL NULL

这样SQL如何实现？

[qian119110]

数据库存储的格式是 CODE NAME1 NAME2 NAME3 1 A1 NULL NULL 1 A11 NULL NULL 2 NULL A2 NULL 3 NULL NULL A3 然后我抽取的数据格式需要 NAME1 NAME2 NAME3 A1 A2 A3 A11 NULL NULL

[dzxccsu]

看不出A1,A2,A3有什么联系

[mayanzs]

with TT as (select 1 code,'A1' name1,null name2,null name3 from dual union all
    select 1 code,'A11' name1,null name2,null name3 from dual union all
    select 2 code,null name1,'A2' name2,null name3 from dual union all
    select 3 code,null name1,null name2,'A3' name3 from dual)
select A.name1,B.name2,C.name3 from (select row_number() over (order by name1) rn,name1 from tt) A
  full outer join (select row_number() over (order by name2) rn,name2 from tt) B on A.rn=B.rn
  full outer join (select row_number() over (order by name3) rn,name3 from tt) C on A.rn=C.rn
  where  not (name1 is null and name2 is null and name3 is null)
  order by 1;

[qian119110]

没什么关系 如果是这样的数据 CODE NAME1 NAME2 NAME3 1 A1 NULL NULL 1 A11 NULL NULL 2 NULL A2 NULL 2 NULL A22 NULL 3 NULL NULL A3 希望合并后数据是 NAME1 NAME2 NAME3 A1 A2 A3 A11 A22 NULL 如果是这样的数据 CODE NAME1 NAME2 NAME3 1 A1 NULL NULL 1 A11 NULL NULL 2 NULL A2 NULL 2 NULL A22 NULL 2 NULL A23 NULL 3 NULL NULL A3 希望合并后数据是 NAME1 NAME2 NAME3 A1 A2 A3 A11 A22 NULL NULL A23 NULL

[小灰狼W]

只是简单地将数据堆在一起，之间没有联系。不符合关系数据库的基本思路。 但是可以做到 SELECT t1.name1,t2.name2,t3.name3 FROM(SELECT ROWNUM rn,name1 FROM 表 WHERE name1 IS NOT NULL)t1 FULL JOIN (SELECT ROWNUM rn,name2 FROM 表 WHERE name2 IS NOT NULL)t2 ON t1.rn=t2.rn FULL JOIN (SELECT ROWNUM rn,name3 FROM 表 WHERE name3 IS NOT NULL) t3 ON NVL(t1.rn,t2.rn)=t3.rn

[qian119110]

引用 3 楼 mayanzs 的回复:

with TT as (select 1 code,'A1' name1,null name2,null name3 from dual union all
    select 1 code,'A11' name1,null name2,null name3 from dual union all
    select 2 code,null name1,'A2' name2,null name3 from dual union all
    select 3 code,null name1,null name2,'A3' name3 from dual)
select A.name1,B.name2,C.name3 from (select row_number() over (order by name1) rn,name1 from tt) A
  full outer join (select row_number() over (order by name2) rn,name2 from tt) B on A.rn=B.rn
  full outer join (select row_number() over (order by name3) rn,name3 from tt) C on A.rn=C.rn
  where  not (name1 is null and name2 is null and name3 is null)
  order by 1;

你是对的