使用 SSMS，看起來像這樣的表
| 代理人 | 地點 |
|---|---|
| 一個 | 1 |
| 一個 | 2 |
| 乙 | 3 |
| 乙 | 4 |
我如何運行查詢以獲取：
| 代理人 | 地點 |
|---|---|
| 一個 | 1,2 |
| 乙 | 3,4 |
uj5u.com熱心網友回復：
您可以嘗試在 Agent 欄位上進行 Self Join，如下所示：
SELECT
AGENT_A as AGENT,
CONCAT(CONCAT(LOCATION_A, ','), LOCATION_B) as LOCATION
FROM (
SELECT
A.AGENT as AGENT_A,
A.LOCATION as LOCATION_A,
B.AGENT as AGENT_B,
B.LOCATION as LOCATION_B
FROM SSMS as A
LEFT JOIN SSMS as B
on A.Agent = B.Agent) as T
WHERE LOCATION_A < LOCATION_B
在這里你可以看到一個Demo：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=00cb9ecb7f0584c2436a0ee6bca6a30b
uj5u.com熱心網友回復：
我使用 SQL Server 2019，但它也適用于 Azure sql db。因此，如果您只想回傳不同的值，我建議使用 rank() over() 丟棄來自其他代理的任何重復值。只有一個缺點，第一個唯一位置將由第一個可用代理接收。
在代碼中更容易理解：
Create table Agents
(
Agent char(1),
Location int
)
insert into Agents
VALUES
('A', 1),
('A', 2),
('A', 6),
('B', 3),
('B', 4),
('C', 1),
('C', 4),
('C', 5)
select Agent, STRING_AGG([Location], ',') WITHIN GROUP (ORDER BY Location ASC) as Locations
from
(
select Agent, Location, rank() over (partition by [Location] order by Agent) as rnk
from Agents
) as t
--will return agents with distinct locations, because they have the rank equals to 1
where t.rnk = 1
group by Agent
這是測驗它的鏈接：SQLize Online
uj5u.com熱心網友回復：
遞回 CTE：
WITH cte1 as (
SELECT
Agent,
CAST(Location AS VARCHAR(MAX)) Location,
row_number() over (partition by Agent order by Location) R
FROM SSMS
),
ctec as (
SELECT Agent, count(*) as c
FROM SSMS
GROUP BY Agent),
cte2 (Agent, Location, i, L) as (
SELECT
Agent,
CONCAT(Location,'') Location,
1 as i ,
Location L
from cte1
where R=1
union all
select
cte2.Agent,
CONCAT(cte2.Location, ',', cte1.Location),
i 1,
cte1.Location
from cte1
inner join cte2 on cte2.Agent=cte1.Agent
and cte1.Location > cte2.Location and cte1.R = i 1
inner join ctec on cte2.Agent= ctec.Agent
where i < ctec.c
)
SELECT Agent,Location
FROM cte2
WHERE i=(select c from ctec where ctec.Agent=cte2.Agent)
ORDER BY Agent;
見：DBFIDDLE
輸出，添加了一些資料：
INSERT INTO SSMS VALUES ('C', '5');
INSERT INTO SSMS VALUES ('C', '6');
INSERT INTO SSMS VALUES ('C', '7');
INSERT INTO SSMS VALUES ('D', '5');
INSERT INTO SSMS VALUES ('D', '3');
INSERT INTO SSMS VALUES ('D', '1');
INSERT INTO SSMS VALUES ('D', '2');
| 代理人 | 地點 |
|---|---|
| 一個 | 1,2 |
| 乙 | 3,4 |
| C | 5,6,7 |
| D | 1,2,3,5 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/410552.html
標籤：
下一篇：如何從長表轉為寬表？