SET SESSION group_concat_max_len = 1000000;
SELECT
cast(
concat(
PCR_M.PCR_Year,
'-',
LPAD(PCR_M.PCR_Id,4,'0')
) as CHAR
) as PCR_Id,
PCR_M.PCR_Name,
PCR_M.PCR_SEV_Id as Severity,
(
select
group_concat(I.ITM_Name separator ", ")
from
LINK_PCR_ITEM ITM
join ITEM I
where
I.ITM_Id=ITM.ITM_Id and ITM.PCR_Year=PCR_M.PCR_Year and ITM.PCR_Id=PCR_M.PCR_Id group by 'ITM_Id'
) as CSCI,
(
select group_concat(
"[Start][",
(select USR_Name from USERS where PAN.PAN_Author=USR_Id),
"] [",
date_format(PAN.PAN_Modification_Date, "%Y%m%d %H:%i"),
"]\r\n",
PAN.PAN_Comment,
"\r\n[End][",
(select USR_Name from USERS where PAN.PAN_Author=USR_Id),
"] [",
date_format(PAN.PAN_Modification_Date, "%Y%m%d %H:%i"),
"]"
separator "\r\n\r\n"
)
from PCR_ANALYSIS PAN
where (PCR_M.PCR_Id=PAN.PCR_Id and PCR_M.PCR_Year=PAN.PCR_Year)
) as Analysis,
(
select group_concat(
"[Start][",
(select USR_Name from USERS where PCT.PCT_Author=USR_Id),
"] [",
date_format(PCT.PCT_Modification_Date, "%Y%m%d %H:%i"),
"]\r\n",
PCT.PCT_Comment,
"\r\n[End][",
(select USR_Name from USERS where PCT.PCT_Author=USR_Id),
"] [",
date_format(PCT.PCT_Modification_Date, "%Y%m%d %H:%i"),
"]"
separator "\r\n\r\n"
)
from PCR_CORRECTIVE PCT
where (PCR_M.PCR_Id=PCT.PCR_Id and PCR_M.PCR_Year=PCT.PCR_Year)
) as Corrective
from PCR_MAIN PCR_M;
