行列转置，left join 可以实现不同行相应列的合并

行列转置（max --用文字合并空白），left join合并

 

SELECT
"服务日期" ,
"服务时间",
"用户名",
"企业名称",
"跟踪人",
"企业类型",
"权限",
"频道名称",
"栏目名称",
"品种名称",
"新闻标题",
"发布人",
"发布时间",
"总访问时间",
"访问IP",
COUNT("用户名") 
FROM
(
select 
event_id as "事件id",
day  as "服务日期",
server_time as "服务时间",
user_name as "用户名",
max(
  CASE
    WHEN (event_property_code='102101000100001001') THEN
  event_property_value
  ELSE
   NULL
  end
) as "企业名称",
max(
  CASE
    WHEN (event_property_code='102101000100001002') THEN
  event_property_value
  ELSE
   NULL
  end
) as "企业类型",
max(
  CASE
    WHEN (event_property_code='102101000100001004') THEN
  event_property_value
  ELSE
   NULL
  end
) as "跟踪人",
max(
  CASE
    WHEN (event_property_code='102101000100001003') THEN
  event_property_value
  ELSE
   NULL
  end
) as "权限",
max(
  CASE
    WHEN (event_property_code='102101000100001005') THEN
  event_property_value
  ELSE
   NULL
  end
) as "频道名称",
max(
  CASE
    WHEN (event_property_code='102101000100001006') THEN
  event_property_value
  ELSE
   NULL
  end
) as "栏目名称",
max(
  CASE
    WHEN (event_property_code='102101000100001007') THEN
  event_property_value
  ELSE
   NULL
  end
) as "品种名称",
max(
  CASE
    WHEN (event_property_code='102101000100001008') THEN
  event_property_value
  ELSE
   NULL
  end
) as "新闻标题",
max(
  CASE
    WHEN (event_property_code='102101000100001010') THEN
  event_property_value
  ELSE
   NULL
  end
) as "发布人",
max(
  CASE
    WHEN (event_property_code='102101000100001009') THEN
  event_property_value
  ELSE
   NULL
  end
) as "发布时间", 
max(
  CASE
    WHEN (event_property_code='102101000100001011') THEN
  event_property_value
  ELSE
   NULL
  end
) as "总访问时间",
max(
  CASE
    WHEN (event_property_code='102101000100001012') THEN
  event_property_value
  ELSE
   NULL
  end
) as "访问IP"
from (SELECT  mm.*,ss.* FROM cms.daily_new_clientrpt_master mm LEFT JOIN cms.daily_new_clientrpt_slave ss on mm.event_id= ss.event_id  WHERE pro_code='102'  ) T  GROUP BY "事件id","服务日期","服务时间","用户名" ORDER BY "事件id","服务日期" )
GROUP BY "新闻标题","发布人","用户名","企业名称","跟踪人","企业类型","权限","频道名称","栏目名称","品种名称","发布时间","总访问时间","访问IP","服务时间","服务日期" ;