我可以讓多條記錄在同一條記錄中顯示值嗎

我正在使用以下查詢來顯示客戶及其殘疾的記錄。查詢有效，但結果顯示同一案例和客戶的多條記錄，其中他們有多種殘疾。有沒有辦法將所有殘疾都包含在一個記錄下，這樣它就不會因為資料的微小變化而復制整個記錄？

以下是我在當前狀態下的查詢：

USE MyPersonalSupport_reporting

SELECT 

SC.Name AS 'Sub-Contract',
CSCH.Received AS LiveDate,
CS.ServiceEndDate AS ServiceEndDate,
CS.CaseReference,
CONTACT.FirstName AS 'Forename',
CONTACT.LastName AS 'Surname',
CONTACT.DateOfBirth AS DOB,
CONTACT.DateOfDeath,
CONTACT.Age,
CCAV.ConcatenatedAddress AS 'Full Address',
LK1.Value AS Ethnicity,
LK2.Value AS Sex,
LK3.Value AS Religion,
LK4.Value AS Sexuality,
LK5.Value AS Transgender,
LK6.Value AS Nationality,
LK7.Value AS 'First Language',
SO.Name AS ServiceOffering,
LK.Value AS CaseStatus,
DATEDIFF(day, CSCH.Received, CS.serviceenddate) AS 'Days Occupied',
CONCAT (EMP.FirstName, ' ' , EMP.LastName) AS KeyWorker, 
CASE WHEN CONTACT.HasDisibility = 1 THEN 'YES' ELSE 'NO' END AS HasDisability,
CASE WHEN DV.value = 'Autistic Spectrum Condition' THEN 'YES' ELSE 'NO' END AS AutisticSpectrumCondition,
CASE WHEN DV.value = 'Hearing Impairment' THEN 'YES' ELSE 'NO' END AS 'Hearing Impairment',
CASE WHEN DV.value = 'Learning Disability' THEN 'YES' ELSE 'NO' END AS 'Learning Disability',
CASE WHEN DV.value = 'Mental Health' THEN 'YES' ELSE 'NO' END AS 'Mental Health',
CASE WHEN DV.value = 'Mobility Disability' THEN 'YES' ELSE 'NO' END AS 'Mobility Disability',
CASE WHEN DV.value = 'Progressive Disability / Chronic Illness' THEN 'YES' ELSE 'NO' END AS 'Progressive Disability / Chronic Illness',
CASE WHEN DV.value = 'Visual Impairment' THEN 'YES' ELSE 'NO' END AS 'Visual Impairment',
CASE WHEN DV.value = 'Other' THEN 'YES' ELSE 'NO' END AS 'Other Disability',
CASE WHEN DV.value = 'Does not wish to disclose' THEN 'YES' ELSE 'NO' END AS 'Does not wish to disclose'



FROM [MyPersonalSupport_reporting].[Mps].[Cases] AS CS

INNER JOIN mps.CaseContracts AS CC ON CS.caseid = CC.caseid 
INNER JOIN mps.CaseStatusChangeHistories AS CSCH ON CS.CaseId = CSCH.CaseId
INNER JOIN mps.Contacts AS CONTACT ON CS.CustomerId = CONTACT.ContactId
FULL OUTER JOIN mps.ContactCurrentAddress AS CCAV ON CONTACT.ContactID = CCAV.ContactId
FULL OUTER JOIN mps.LookupItems AS LK ON CSCH.StatusId = LK.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK1 ON CONTACT.EthnicityId = LK1.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK2 ON CONTACT.SexId = LK2.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK3 ON CONTACT.ReligionId = LK3.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK4 ON CONTACT.SexualityId = LK4.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK5 ON CONTACT.TransgenderId = LK5.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK6 ON CONTACT.NationalityId = LK6.LookupItemId
FULL OUTER JOIN mps.LookupItems AS LK7 ON CONTACT.FirstLanguageId = LK7.LookupItemId
FULL OUTER JOIN mps.SubContracts AS SC ON CC.SubContractId = SC.SubContractId
FULL OUTER JOIN mps.ServiceOfferings AS SO ON SC.ServiceOfferingId = SO.ServiceOfferingId
FULL OUTER JOIN mps.Employees AS EMP ON EMP.EmployeeId = CS.KeyWorkerId
FULL OUTER JOIN dbo.disabilitiescrosstab AS DV ON CONTACT.ContactId = DV.EntityID 




WHERE 

CSCH.Received >= '2000-01-01' AND CS.ServiceEndDate <= GETDATE()

AND CSCH.StatusId = 1392

AND CSCH.Archived = 0

AND CONTACT.Archived = 0

ORDER BY CS.CaseId

uj5u.com熱心網友回復：

看來您需要的是一個由兩部分組成的查詢。一次讓每個“聯系人”有殘疾的人已經與所有殘疾預先聚合，因此結果是每個聯系人的單個記錄。然后加入案例。此外，您的查找表具有 FULL OUTER JOINS。它不像有人會被允許對種族、性別、宗教、性取向有多個查找值——他們會嗎？但在當今與事物相關聯的時代，可能嗎？但我認為目的是一個人只能被歸入一個類別，否則對于種族、性取向、跨性別等的每種組合，你都會有多個記錄......所以，讓我們按照預期進行查找，因為只有一個值曾經存在。這些應該只是到相應查找表的 INNER JOINS，而不是 FULL OUTER joins。

請注意，此查詢僅與聯系人及其相應的查找表/殘疾相關聯，并且僅按聯系人 ID 分組。因此，無論每個聯系人有多少殘疾，它都會產生一個記錄。

SELECT 
        CONTACT.ContactId,
        max( CONTACT.FirstName ) 'Forename',
        max( CONTACT.LastName ) 'Surname',
        max( CONTACT.DateOfBirth ) DOB,
        max( CONTACT.DateOfDeath ) DateOfDeath,
        max( CONTACT.Age ) Age,
        max( CCAV.ConcatenatedAddress ) 'Full Address',
        max( LK1.Value  ) Ethnicity,
        max( LK2.Value  ) Sex,
        max( LK3.Value  ) Religion,
        max( LK4.Value  ) Sexuality,
        max( LK5.Value  ) Transgender,
        max( LK6.Value  ) Nationality,
        max( LK7.Value  ) 'First Language',
        max( CASE WHEN CONTACT.HasDisibility = 1 THEN 'YES' ELSE 'NO' END ) HasDisability,
        max( CASE WHEN DV.value = 'Autistic Spectrum Condition' THEN 'YES' ELSE 'NO' END ) AutisticSpectrumCondition,
        max( CASE WHEN DV.value = 'Hearing Impairment' THEN 'YES' ELSE 'NO' END ) 'Hearing Impairment',
        max( CASE WHEN DV.value = 'Learning Disability' THEN 'YES' ELSE 'NO' END ) 'Learning Disability',
        max( CASE WHEN DV.value = 'Mental Health' THEN 'YES' ELSE 'NO' END ) 'Mental Health',
        max( CASE WHEN DV.value = 'Mobility Disability' THEN 'YES' ELSE 'NO' END ) 'Mobility Disability',
        max( CASE WHEN DV.value = 'Progressive Disability / Chronic Illness' THEN 'YES' ELSE 'NO' END ) 'Progressive Disability / Chronic Illness',
        max( CASE WHEN DV.value = 'Visual Impairment' THEN 'YES' ELSE 'NO' END ) 'Visual Impairment',
        max( CASE WHEN DV.value = 'Other' THEN 'YES' ELSE 'NO' END ) 'Other Disability',
        max( CASE WHEN DV.value = 'Does not wish to disclose' THEN 'YES' ELSE 'NO' END ) 'Does not wish to disclose'
    FROM 
        mps.Contacts AS CONTACT 
            JOIN dbo.disabilitiescrosstab AS DV 
                ON CONTACT.ContactId = DV.EntityID 
            JOIN mps.ContactCurrentAddress AS CCAV 
                ON CONTACT.ContactID = CCAV.ContactId
            JOIN mps.LookupItems AS LK1 
                ON CONTACT.EthnicityId = LK1.LookupItemId
            JOIN mps.LookupItems AS LK2 
                ON CONTACT.SexId = LK2.LookupItemId
            JOIN mps.LookupItems AS LK3 
                ON CONTACT.ReligionId = LK3.LookupItemId
            JOIN mps.LookupItems AS LK4 
                ON CONTACT.SexualityId = LK4.LookupItemId
            JOIN mps.LookupItems AS LK5 
                ON CONTACT.TransgenderId = LK5.LookupItemId
            JOIN mps.LookupItems AS LK6 
                ON CONTACT.NationalityId = LK6.LookupItemId
            JOIN mps.LookupItems AS LK7
                ON CONTACT.FirstLanguageId = LK7.LookupItemId
    WHERE
        -- put the ARCHIVED FILTER HERE so it only includes active vs everyone in the system.
        CONTACT.Archived = 0
    GROUP BY
        CONTACT.ContactId

現在，您可以采用上述查詢并使用 IT 作為子選擇查詢，以在最終獲得其部分

SELECT 
        SC.Name AS 'Sub-Contract',
        CSCH.Received AS LiveDate,
        CS.ServiceEndDate AS ServiceEndDate,
        CS.CaseReference,
        OneContact.Forename,
        OneContact.Surname,
        OneContact.DOB,
        OneContact.DateOfDeath,
        OneContact.Age,
        OneContact.[Full Address],
        OneContact.Ethnicity,
        OneContact.Sex,
        OneContact.Religion,
        OneContact.Sexuality,
        OneContact.Transgender,
        OneContact.Nationality,
        OneContact.[First Language],
        SO.Name AS ServiceOffering,
        LK.Value AS CaseStatus,
        DATEDIFF(day, CSCH.Received, CS.serviceenddate) AS 'Days Occupied',
        CONCAT (EMP.FirstName, ' ' , EMP.LastName) AS KeyWorker, 
        OneContact.HasDisability,
        OneContact.AutisticSpectrumCondition,
        OneContact.[Hearing Impairment],
        OneContact.[Learning Disability],
        OneContact.[Mental Health],
        OneContact.[Mobility Disability],
        OneContact.[Progressive Disability / Chronic Illness],
        OneContact.[Visual Impairment],
        OneContact.[Other Disability],
        OneContact.[Does not wish to disclose]
    FROM 
        Mps.Cases AS CS
            INNER JOIN mps.CaseContracts AS CC 
                ON CS.caseid = CC.caseid 
            INNER JOIN mps.CaseStatusChangeHistories AS CSCH 
                ON CS.CaseId = CSCH.CaseId
            INNER JOIN 
            ( complete first query above) OneContact
                ON CS.CustomerId = OneContact.ContactId
            JOIN mps.LookupItems LK 
                ON CSCH.StatusId = LK.LookupItemId
            JOIN mps.SubContracts SC 
                ON CC.SubContractId = SC.SubContractId
            JOIN mps.ServiceOfferings SO 
                ON SC.ServiceOfferingId = SO.ServiceOfferingId
            JOIN mps.Employees EMP 
                ON EMP.EmployeeId = CS.KeyWorkerId
    WHERE 
            CSCH.Received >= '2000-01-01' 
        AND CS.ServiceEndDate <= GETDATE()
        AND CSCH.StatusId = 1392
        AND CSCH.Archived = 0
    ORDER BY
        CS.CaseId

Now, one suggestion for column names. I do not recommend having final column names with any spaces or special characters such as you have, especially with the ones like [Progressive Disability / Chronic Illness], and [Does not wish to disclose]. They should be normal, no spaces. The formatting / output should be handled by the output process to put in such headers and such. More headache when you need to explicitly tack quotes/brackets around column names, especially if you miss spaces. Instead, have column name as: ProgressiveDisabilityChronicIllness and ChronicIllness. Your output routines can split the column headings.

補充一點。無論情況如何，第一個查詢都將處理所有聯系人。但是這些案例只會拉動那些符合條件的案例。如果您發現您的查詢因為所有聯系人而花費太多時間，您可以通過限制聯系人符合條件的情況來更新它。至少在查詢聯系人時，我確實在那里包含了 archived = 0 狀態。

uj5u.com熱心網友回復：

雖然我不完全了解您的用例，但我相信沒有必要進行完全外連接，您可以使用左連接/內連接。

第 1 步：您可以STRING_AGG用于"DisibilityName"列。匯總每個物體 ID 的所有疾病

SELECT EntityID, STRING_AGG(DV_VALUE,",") 
FROM 
dbo.disabilitiescrosstab
GROUP BY EntityID

第 2 步：然后將聚合表與您的基表連接起來

代碼（洗掉了幾個連接，但想法是一樣的）：

SELECT 

SC.Name AS 'Sub-Contract',
CSCH.Received AS LiveDate,
CS.ServiceEndDate AS ServiceEndDate,
CS.CaseReference,
CONTACT.FirstName AS 'Forename',
CONTACT.LastName AS 'Surname',
CONTACT.DateOfBirth AS DOB,
CONTACT.DateOfDeath,
CONTACT.Age,
CCAV.ConcatenatedAddress AS 'Full Address',
DV.VALUE AS DisibilityName

FROM [MyPersonalSupport_reporting].[Mps].[Cases] AS CS

LEFT JOIN mps.CaseContracts AS CC ON CS.caseid = CC.caseid 
LEFT JOIN mps.CaseStatusChangeHistories AS CSCH ON CS.CaseId = CSCH.CaseId
LEFT JOIN mps.Contacts AS CONTACT ON CS.CustomerId = CONTACT.ContactId
LEFT JOIN mps.ContactCurrentAddress AS CCAV ON CONTACT.ContactID = CCAV.ContactId
LEFT JOIN (SELECT EntityID, STRING_AGG(DV_VALUE,",") FROM dbo.disabilitiescrosstab GROUP BY EntityID) AS DV ON CONTACT.ContactId = DV.EntityID 


WHERE 
CSCH.Received >= '2000-01-01' AND CS.ServiceEndDate <= GETDATE()
AND CSCH.StatusId = 1392
AND CSCH.Archived = 0
AND CONTACT.Archived = 0
ORDER BY CS.CaseId

標籤：sql sql-server

上一篇：SQL匹配持續時間（fromDatetime-toDatetime）

下一篇：沒有鍵連接的查詢查找表