sql - PostgreSQL - 创建临时列而不是多行

Question

我正在处理 PostgreSQL 客户记录。

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

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

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

当我加入并关联数据时，我会为每个客户 ID 提取多条记录(如果每个客户有超过 1 种通信类型，例如电话和电子邮件)。

我如何才能不为每种通信类型创建另一行，而是将信息放入一个临时列中，例如电话列、传真列和电子邮件列 - 然后每个客户只有 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 (等)、billing.city、billing.state、billing.zip、party.contactperson(作为 party.firstname + party.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

实际上，INNER JOIN 并不是我所需要的...我需要按照下面的描述进行 LEFT JOIN。

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 ...