尝试在新视图中的一行中列出客户详细信息，从两行中绘制信息，我发现我得到了带有空值的重复行

发布于 2024-12-12 12:06:12 字数 513 浏览 0 评论 0原文

尝试从另外两个表创建一个新视图，标题为客户信息。

目前的声明现在显示

select c.customer_id, c.name, 
max(decode(cat.method_id, 'Phone', cat.value))phone,
max(decode(cat.method_id, 'Fax', cat.value))fax, 
max(decode(cat.method_id, 'Mobile', cat.value))mobile,
max(decode(cat.method_id, 'E-Mail', cat.value))email
from table_1 c, table_2 cat
where c.customer_id = cat.customer_id
group by c.customer_id, c.name

该声明工作正常，一个客户的所有内容都在一行上，但是如果客户有 2 个电话号码记录，我希望将其连接在第一个电话列中，我尝试对值进行排名并使用它们在子选择中，但似乎无法让它们一起工作。

原文

trying to create a new view from 2 other tables, titled customer information.

currently statement reads

select c.customer_id, c.name, 
max(decode(cat.method_id, 'Phone', cat.value))phone,
max(decode(cat.method_id, 'Fax', cat.value))fax, 
max(decode(cat.method_id, 'Mobile', cat.value))mobile,
max(decode(cat.method_id, 'E-Mail', cat.value))email
from table_1 c, table_2 cat
where c.customer_id = cat.customer_id
group by c.customer_id, c.name

now the statement works fine and everything for one customer is on a single row however where the customer has 2 phone number records, i would like it to be concatinated in the first phone column, I tried to rank the values and use them in a subselect but cannot seem to get it all working together.

分享到QQ

分享到微博