后greSQL - 创建临时列而不是多行

Question

我正在处理 PostgreSQL 客户记录。

我的任务是导出客户记录。

除了有联系信息的客户之外，我还有另一张桌子。

每行一个联系人项目（电话、值或电子邮件、值等）。

当我加入并关联数据时，我会为每个客户ID提取多个记录（如果每个客户有一个以上的通信类型，例如电话和电子邮件）。

我怎么能不为每种通信类型再做一行，而是将信息放入临时列（如电话列，传真列和电子邮件列）中 - 然后每个客户只有1行。

编辑 - 你们是大师

带列的数据库表：

account {
  id
  accountid
  title (company name?)
  shiptoaddress_id (links to address table)
  billtoaddress_id (links to address table)
}

address {
  city
  country
  state
  name (company name?)
  street1
  street2
  street3
  zip
  id
}

comlink {
  isdeleted
  type [Phone,Fax,E-mail,Cell,IM,FaceBook,Twitter,LinkedIn,Web Site,Other]
  value
  id
  party_id (links to party table)
}

party {
  isdeleted [t,f]
  firstname
  lastname
  prefix
  salutation
  suffix
  id
  address_id
  jobtitle_id
}

party_comlinks {
  party_id (links to party table ex: fname lname...)
  comlinks_id (links to comlink table phone, email, etc 1 item per row)
}

因此，我想做的是使用以下数据拉动所有客户：

customer.id，customer.accountid，customer.title，shipping.name，shipping.street1，shipping.street2，shipping.street3，shipping.city，shipping.state，shipping.zip，billing.name，billing.street1（etc），billing.city，billing.state，billing.zip，party.contactperson（party.firstnameparty.lastname）并有电话，电子邮件，传真

我不确定这是否可能，因为在系统中，我认为您可以为每个客户提供多个运输信息，账单信息，联系信息...但是，如果每个运输信息和帐单信息相同，我只想要一行电话，电子邮件，传真等，而不是每个电话，电子邮件，传真等的多行。

清澈如泥，对吗？：-）

编辑-现在可能已经得到了，但仍将感谢输入

SELECT account.id, account.accountid, account.status, account.title AS "customer",
party.firstname AS "firstname", party.lastname as "lastname", address.name AS "billname",
address.street1 AS "billtostreet1", address.street2 AS "billtostreet2", address.city as "billtocity",
address.state AS "billtostate", address.zip AS "billtozip", address2.name AS "shiptoname",
address2.street1 as "shiptostreet1", address2.street2 AS "shiptostreet2", address2.city AS "shiptocity",
address2.state AS "shiptostate", address2.zip AS "shiptozip", 

((SELECT a.value 
FROM public.comlink a, party_comlinks b
WHERE b.party_id=party.id AND b.comlinks_id=a.id AND a.type='Phone')) AS "phone",

((SELECT a.value 
FROM public.comlink a, party_comlinks b
WHERE b.party_id=party.id AND b.comlinks_id=a.id AND a.type='Fax')) AS "fax",

((SELECT a.value 
FROM public.comlink a, party_comlinks b
WHERE b.party_id=party.id AND b.comlinks_id=a.id AND a.type='E-Mail')) AS "email"

FROM  ( public.account account 
   INNER JOIN public.party party ON account.contact_id = party.id )  
   INNER JOIN public.comlink comlink ON party.id = comlink.party_id 
   INNER JOIN public.address address ON account.billtoaddress_id = address.id
   INNER JOIN public.contact contact ON account.contact_id = contact.id
   LEFT JOIN public.contact_shiptoaddress contact_shiptoaddress ON contact.id = contact_shiptoaddress.contact_id
   LEFT JOIN public.address address2 ON contact_shiptoaddress.shiptoaddress_id = address2.id



WHERE account.isdeleted = 'f'

--WHERE ((comlink.type = 'E-Mail')) 
    --AND ((account.walkin is null OR (NOT ( account.walkin ))))
    --AND ((NOT ( (account."status" = 'CustomerStatusInactive') )))
    --AND ((account."prospect" is null
    --OR (NOT ( account."prospect" )))) 

ORDER BY account.id ASC

实际上，内部连接不是我所需要的...我需要做左连接，如下所述。

Answer 1

您必须通过多次显式连接联系表来实现这一点。现在有了一种根据数据“动态”设置输出列的方法。

例子：

SELECT customers.name, phone_contacts.value AS phone, fax_contacts.value AS fax, ...
FROM customers
  LEFT JOIN (SELECT * FROM contacts WHERE contact_type = 'phone') AS phone_contacts ON ...
  LEFT JOIN (SELECT * FROM contacts WHERE contact_type = 'fax') AS fax_contacts ON ...