问创建SQL查询以将多个相关行组合在一起

EN

SELECT 
  BASE.CUST_ID,
  SITE.CONTACT_NAME AS SITE_NAME,
  SITE.ADDRESS AS SITE_ADDRESS,
  SITE.CITY AS SITE_CITY,
  BUILDER.CONTACT_NAME AS BUILDER_NAME,
  BUILDER.ADDRESS AS BUILDER_ADDRESS,
  BUILDER.CITY AS BUILDER_CITY,
  ARCHITECT.CONTACT_NAME AS ARCHITECT_NAME,
  ARCHITECT.ADDRESS AS ARCHITECT_ADDRESS,
  ARCHITECT.CITY AS ARCHITECT_CITY
FROM (SELECT DISTINCT CUST_ID FROM TABLE_YOU_DID_NOT_NAME) BASE
LEFT JOIN TABLE_YOU_DID_NOT_NAME SITE ON BASE.CUST_ID = SITE.CUST_ID AND SITE.ADDRESS_TYPE = 'SITE'
LEFT JOIN TABLE_YOU_DID_NOT_NAME BUILDER ON BASE.CUST_ID = BUILDER.CUST_ID AND BUILDER.ADDRESS_TYPE = 'BUILDER'
LEFT JOIN TABLE_YOU_DID_NOT_NAME ARCHITECT ON BASE.CUST_ID = ARCHITECT.CUST_ID AND ARCHITECT.ADDRESS_TYPE = 'ARCHITECT'

select cust_id,
       max(case when address_type = 'Site' then Contact_Name end) as site_name,
       max(case when address_type = 'Site' then Town end) as site_town,
       max(case when address_type = 'Site' then Address end) as site_address,
       max(case when address_type = 'Builder' then Contact_Name end) as builder_name,
       max(case when address_type = 'Builder' then Town end) as builder_town,
       max(case when address_type = 'Builder' then Address end) as builder_address,
      . . .
from t
group by cust_id;

select * from addresses 
pivot ( 
  MAX(CONTACT_NAME) CONTACT_NAME, 
  MAX(ADDRESS) ADDRESS, 
  MAX(CITY) CITY 
  FOR ADDRESS_TYPE IN ('SITE','BUILDER','ARCHITECT'));