SQL Server 同表兩行數據按編號合並為一行：自連接 + 條件聚合實現橫向展開

源表結構

id  name  bianhao  address  shijian
1   張三   001      北京      2013-12-1
2   李四   001      北京      2013-12-1

目標

name1  name2  bianhao  address  shijian
張三    李四    001      北京      2013-12-1

方案一：自連接 (固定兩行)

SELECT t1.name AS name1, t2.name AS name2, t1.bianhao, t1.address, t1.shijian
FROM tbl t1
JOIN tbl t2 ON t1.bianhao = t2.bianhao AND t1.address = t2.address
           AND t1.id < t2.id; -- 避免重複與自連接

方案二：條件聚合 (動態行數，SQL Server 2017+)

SELECT bianhao, address, shijian,
       STRING_AGG(name, ' ') WITHIN GROUP (ORDER BY id) AS names
FROM tbl
GROUP BY bianhao, address, shijian;

若需固定列名：

SELECT bianhao, address, shijian,
       MAX(CASE WHEN rn=1 THEN name END) AS name1,
       MAX(CASE WHEN rn=2 THEN name END) AS name2
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY bianhao,address,shijian ORDER BY id) AS rn
    FROM tbl
) t
GROUP BY bianhao, address, shijian;