mysql - 将日期行转换为sql中的列

Question

我有一个包含数据的表格，如下所示。我需要将日期行转换为列，并且需要填充相应日期中的契约(Contract)计数。请帮助获取代码。这是表XXX中的数据

预期输出为

请做需要的事情

这是我尝试过的代码。但出现数据类型错误。

    DECLARE @Proposed_Delv_Dt AS datetime
SELECT @Proposed_Delv_Dt = 
    COALESCE(@Proposed_Delv_Dt + ', ', '') +  CAST(@Proposed_Delv_Dt AS datetime)
    FROM ( select distinct Proposed_Delv_Dt from (select  PM.Project_Alias_name as 'Project Name', CD.Key_column_values as contractNumber, PLM.Type_of_picklist as Lease_type, 
PLM.Pick_List_description as TypeOfWork, PWS.Workflow_status_description as Contract_status,
  UM.Employee_id, SM.SBU_Name, UM.fULL_NAME as Assigned_To, UMAB.Full_Name as 
assigned_By, RM.Role_Description as role, CUD.Change_date as AssignedDate, CD.Proposed_delivery_date as 'Proposed_Delv_Dt'
from Contract_user_details CUD
left join Contract_Details CD on CD.Pkey = CUD.Fkey_Contract_Key
left join Project_Master PM on CD.Fkey_Project_Key = PM.Pkey
left join SBU_Master SM on SM.Pkey = PM.Fkey_Sbu_key
left join Pick_list_master PLM on CD.Fkey_Lease_Type = PLM.Pkey
left join Project_Workflow_Status PWS on CD.Fkey_Current_Workflow_Status = PWS.Pkey
left join Users_Master UM on CUD.Fkey_User_key = UM.Pkey
left join Users_Master UMAB on CUD.Fkey_assigned_by = UMAB.Pkey
left join Role_master RM on RM.pkey = CUD.Fkey_Role_key)f )d
SELECT @Proposed_Delv_Dt Dates

我期待以“,”(逗号)分隔的日期的 o/p

Answer 1

DECLARE @Proposed_Delv_Dt AS NVARCHAR (MAX)
SELECT @Proposed_Delv_Dt = 
    COALESCE(@Proposed_Delv_Dt + ',[ ' + CONVERT(NVARCHAR, [Proposed_Delv_Dt], 112) + ']','[' + CONVERT(NVARCHAR, [Proposed_Delv_Dt], 112) + ']')
    FROM ( select distinct Proposed_Delv_Dt from (select  PM.Project_Alias_name as 'Project Name', CD.Key_column_values as contractNumber, PLM.Type_of_picklist as Lease_type, 
PLM.Pick_List_description as TypeOfWork, PWS.Workflow_status_description as Contract_status,
UM.Employee_id, SM.SBU_Name,UM.fULL_NAME as Assigned_To, UMAB.Full_Name as 
assigned_By, RM.Role_Description as role, CUD.Change_date as AssignedDate, CD.Proposed_delivery_date as Proposed_Delv_Dt
from Contract_user_details CUD
left join Contract_Details CD on CD.Pkey = CUD.Fkey_Contract_Key
left join Project_Master PM on CD.Fkey_Project_Key = PM.Pkey
left join SBU_Master SM on SM.Pkey = PM.Fkey_Sbu_key
left join Pick_list_master PLM on CD.Fkey_Lease_Type = PLM.Pkey
left join Project_Workflow_Status PWS on CD.Fkey_Current_Workflow_Status = PWS.Pkey
left join Users_Master UM on CUD.Fkey_User_key = UM.Pkey
left join Users_Master UMAB on CUD.Fkey_assigned_by = UMAB.Pkey
left join Role_master RM on RM.pkey = CUD.Fkey_Role_key)f )d

DECLARE @DynamicPIVOT AS VARCHAR(MAX)
SELECT @DynamicPIVOT = 'SELECT  SBU_name, Contract_status, Assigned_to, Role, ' + @Proposed_Delv_Dt + 
' FROM (
    select  PM.Project_Alias_name as Project_Name, CD.Key_column_values as contractNumber, PLM.Type_of_picklist as Lease_type, 
PLM.Pick_List_description as TypeOfWork, PWS.Workflow_status_description as Contract_status,
UM.Employee_id, SM.SBU_Name,UM.fULL_NAME as Assigned_To, UMAB.Full_Name as 
assigned_By, RM.Role_Description as role, CUD.Change_date as AssignedDate, CD.Proposed_delivery_date as Proposed_Delv_Dt
from Contract_user_details CUD

left join Contract_Details CD on CD.Pkey = CUD.Fkey_Contract_Key
left join Project_Master PM on CD.Fkey_Project_Key = PM.Pkey
left join SBU_Master SM on SM.Pkey = PM.Fkey_Sbu_key
left join Pick_list_master PLM on CD.Fkey_Lease_Type = PLM.Pkey
left join Project_Workflow_Status PWS on CD.Fkey_Current_Workflow_Status = PWS.Pkey
left join Users_Master UM on CUD.Fkey_User_key = UM.Pkey
left join Users_Master UMAB on CUD.Fkey_assigned_by = UMAB.Pkey
left join Role_master RM on RM.pkey = CUD.Fkey_Role_key)f 
PIVOT (
    count(contractNumber) FOR Proposed_Delv_Dt IN (' + @Proposed_Delv_Dt + ')
) Result;'

EXEC (@DynamicPIVOT)

我是从上面的代码到达的...但我只有一个问题。我需要一个总计作为丢失的列，其中日期列中的计数总和如下。

我还需要按 SbU_name、contract_status、Assigned_to、Role 进行分组