July 5, 2022

Lemon Law

Learn ethics in law

Use String_AGG to query with condition in SQL?

I have 3 tables with relationship is Policy (N) -> PolicyService <- Service (N):

DXBusinessPolicy_Policy

ID Code Name
1 COMBO.2103001 [Giá nền] T9/2020 #1
2 IPTV-0121.002 [Giá nền] T8/2020 #1
3 INT.2103001 Chính sách 2

DXBusinessPolicy_Service

ID Code Name
1 INT Internet
2 IPTV IPTV

DXBusinessPolicy_PolicyService

ID PolicyID ServiceID
1 1 1
2 1 2
3 2 2
4 3 1

The question: enter input service (ServiceCode), the output are PolicyID, PolicyCode, PolicyName and a list of services of that policy (string of list ServiceCode join with “,”).

For example: My input is: “INT”. Result expect:

PolicyCode PolicyName Services
COMBO.2103001 [Giá nền] T9/2020 #1 INT,IPTV
INT.2103001 Chính sách 2 INT

I tried to solve this question as follows:

ALTER PROC FindPolicyByService
    @ServiceCode varchar(200)
AS 
BEGIN
    SELECT dbo.DXBusinessPolicy_Policy.ID AS PolicyID,
           dbo.DXBusinessPolicy_Policy.Code AS PolicyCode,
           dbo.DXBusinessPolicy_Policy.Name AS PolicyName,
           STRING_AGG(dbo.DXBusinessPolicy_Service.Code, ',') 
    FROM dbo.DXBusinessPolicy_Policy 
            join dbo.DXBusinessPolicy_PolicyService ON dbo.DXBusinessPolicy_Policy.ID = dbo.DXBusinessPolicy_PolicyService.PolicyID
            join dbo.DXBusinessPolicy_Service ON dbo.DXBusinessPolicy_PolicyService.ServiceID = dbo.DXBusinessPolicy_Service.ID
    WHERE dbo.DXBusinessPolicy_Service.Code = @ServiceCode
    GROUP by dbo.DXBusinessPolicy_Policy.ID, dbo.DXBusinessPolicy_Policy.Code, dbo.DXBusinessPolicy_Policy.Name
END

exec FindPolicyByService "INT"

But the result is not what I expected

PolicyCode PolicyName Services
COMBO.2103001 [Giá nền] T9/2020 #1 INT
INT.2103001 Chính sách 2 INT