Azure SQL 中的條件 String_Agg

我有一張桌子Points。

CREATE TABLE Points(
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Label varchar(50) NULL,
Destination bit NOT NULL,
PickUp bit NOT NULL,
SetDown bit NOT NULL)
)

使用如下數據：

| ID | Label      | PickUp | SetDown | Destination |
|----|------------|--------|---------|-------------|
| 1  | Seattle    | 1      |         |             |
| 2  | Denver     | 1      |         |             |
| 3  | Restaurant |        | 1       | 1           |
| 4  | Dave's     | 1      |         |             |
| 5  | Cindy's    | 1      |         |             |
| 6  | Airport    | 1      | 1       |             |
| 7  | School     |        |         | 1           |

和一個父多對多表，稱之為PointParent

CREATE TABLE PointParent(
ID int NOT NULL,
PointID int NOT NULL FOREIGN KEY REFERENCES Points(ID)
)

數據如下：

| ID | PointID |
|----|---------|
| 1  | 1       |
| 1  | 2       |
| 1  | 3       |
| 2  | 4       |
| 2  | 5       |
| 2  | 6       |
| 2  | 7       |

我正在編寫一個視圖來總結點的標籤，按父 ID 分組：

SELECT PointParent.ID, String_Agg(Label, ', ') AS Summary 
FROM Points LEFT OUTER JOIN PointParent ON Points.ID = PointParent.PointID 
GROUP BY PointParent.ID

現在，我需要擴展這個概念並總結所有具有 的點的標籤PickUp = 1，然後是具有 的點的另一個摘要SetDown = 1，以及Destination = 1由 分組的所有點的另一個摘要PointParent.ID。

我現在完成此操作的方式是使用三個子查詢：

SELECT PointParent.ID, PickUp.Summary AS PickUpSummary, SetDown.Summary AS SetDownSummary, 
Destination.Summary AS DestinationSummary 
FROM PointParent LEFT OUTER JOIN (
 SELECT PointParent.ID, String_Agg(Label, ', ') AS Summary 
 FROM Points LEFT OUTER JOIN PointParent ON Points.ID = PointParent.PointID
 WHERE PickUp = 1 
 GROUP BY PointParent.ID) 
AS PickUp ON PickUp.ID = PointParent.ID LEFT OUTER JOIN (
 SELECT PointParent.ID, String_Agg(Label, ', ') AS Summary 
 FROM Points LEFT OUTER JOIN PointParent ON Points.ID = PointParent.PointID 
 WHERE SetDown = 1 
 GROUP BY PointParent.ID)
AS SetDown ON SetDown.ID = PointParent.ID LEFT OUTER JOIN (
 SELECT PointParent.ID, String_Agg(Label, ', ') AS Summary 
 FROM Points LEFT OUTER JOIN PointParent ON Points.ID = PointParent.PointID 
 WHERE Destination = 1 
 GROUP BY PointParent.ID) 
AS Destination ON Destination.ID = PointParent.ID  

這會產生如下結果：

| ID | PickUpSummary            | SetDownSummary | DestinationSummary |
|----|--------------------------|----------------|--------------------|
| 1  | Seattle, Denver          | Restaurant     | Restaurant         |
| 2  | Dave's, Cindy's, Airport | Airport        | School             |

有沒有辦法在沒有三個單獨的子查詢的情況下實現這一點？某種方式可以在表格上一次通過聚合所有三個字元串？

您可以通過單個表訪問來做到這一點（大約是估計成本的一半）。

SELECT pp.ID, 
 PickUpSummary      = STRING_AGG(CASE p.Pickup      WHEN 1 THEN p.Label END, ', '),
 SetDownSummary     = STRING_AGG(CASE p.SetDown     WHEN 1 THEN p.Label END, ', '),
 DestinationSummary = STRING_AGG(CASE p.Destination WHEN 1 THEN p.Label END, ', ')
FROM dbo.PointParent AS pp
LEFT OUTER JOIN dbo.Points AS p
ON pp.PointID = p.ID
GROUP BY pp.ID;

上的索引PointParent (ID, PointID)- 將消除排序操作 - 會更好。

引用自：https://dba.stackexchange.com/questions/236865