ALTER TABLE [AKUZ].[T_HOSPITAL_DESTINATION_LEK_COMPONENT] ADD [Name] varchar(1024) NULL
GO

update lekComp
set Name = (isnull(trn.NameRus, isnull(medService.[Name], isnull(mnn.NameRus, ''))) +
    case 
        when medService.MedicalServiceID is null and mf.[Name] is not null 
        then ' ' + mf.[Name]
        else ''
    end +
    case 
        when doseView.[Value] is not null 
        then ' ' + doseView.[Value]
        else ''
    end
)
from akuz.T_HOSPITAL_DESTINATION_LEK_COMPONENT lekComp 
left join akuz.S_DLO_DoseView doseView on doseView.Id = lekComp.DoseViewNew
left join pharm.T_C_MNN mnn ON mnn.CMnnID = lekComp.CMnn
left join pharm.T_C_TRN trn ON trn.CTrnID = lekComp.CTrn
left join pharm.T_MEDICATION_FORMS mf ON mf.MedicationFormsID = lekComp.MedicationForms
left join akuz.T_MEDICAL_SERVICE medService ON medService.MedicalServiceID = lekComp.[Procedure]
go

ALTER TABLE [AKUZ].[T_HOSPITAL_DESTINATION] ADD [Name] varchar(1024) NULL
GO

update hd
set hd.[Name] = isnull((
    select stuff((
        select ' + ' + (isnull(ctn.NameRus, isnull(medService.[Name], isnull(cmnn.NameRus, ''))) +
            case 
                when medService.MedicalServiceID is null and mf.[Name] is not null 
                then ' ' + mf.[Name]
                else ''
            end +
            case 
                when doseView.[Value] is not null 
                then ' ' + doseView.[Value]
                else ''
            end
        )
        FROM akuz.T_HOSPITAL_DESTINATION_LEK_COMPONENT lek
        left join akuz.S_DLO_DoseView doseView on doseView.Id = lek.DoseViewNew
        left join pharm.T_C_TRN ctn ON lek.CTrn = ctn.CTrnID
        left join akuz.T_MEDICAL_SERVICE medService ON lek.[Procedure] = medService.MedicalServiceID
        left join pharm.T_C_MNN cmnn ON lek.CMnn = cmnn.CMnnID
        left join pharm.T_MEDICATION_FORMS mf ON lek.MedicationForms = mf.MedicationFormsID
        WHERE lek.HospitalDestination = hd.MedicalRecordID
          AND (
              ctn.NameRus IS NOT NULL 
              OR medService.Name IS NOT NULL 
              OR cmnn.NameRus IS NOT NULL
          )
        order by lek.BaseComponent
        for xml path(''), type
    ).value('.', 'NVARCHAR(MAX)'), 1, 3, '')
), '')
from akuz.T_HOSPITAL_DESTINATION hd
go
