sql - 如何在 SQL Server 中仅选择每个外键的第一条记录？

Question

我的联系人详细信息数据库中有如下行:

contactID - bioID - AddressLine1 - City 
393         1       1 nowhere st   toronto
3921        1       2 somewhere st vancouver
3231        2       1 anywhere rd  barrie
1122        2       2 overthere st halifax      

我目前正在将其内部连接到包含名字、姓氏等列的生物表，结果如下所示:

bioid    firstname    lastname    addressline1    city
1        some         guy         1 nowhere st    toronto
1        some         guy         2 somewhere st  vancouver
2        that         girl        1 anywhere rd   barrie
2        that         girl        2 overthere st  halifax

所以我基本上为每个生物获取 2 行。无论如何，我是否可以将其全部选择为一行，如下所示:

bioid    firstname    lastname   addressline1x1   cityx1    addressline1x2    cityx2
1        some         guy        1 nowhere st     toronto   2 somewhere st    vancouver
2        that         girl       1 anywhere rd    barrie    2 overthere st    halifax

感谢任何帮助。

谢谢，托马斯

编辑:

非常感谢丹尼斯和贾斯汀，我能够解决这个问题。然而，现在我有了另一个。

我想实际选择地址作为 1 个字段，例如:

bioid    firstname    lastname    primary address         secondary address
1        some         guy         1 nowhere st, toronto   2 somewhere st, vanvouver
2        that         girl        1 anywhere rd, barrie   2 overthere st, halifax

我知道我可以通过连接列来做到这一点，例如:

cd1.addressline1 + ', ' cd1.city AS 'Primary Address' 

但是，记录中的某些字段为空，例如，如果没有辅助地址 - 那么如何才能使辅助地址不输出“，”？这些字段是空字符串，而不是 NULL 值。

再次感谢!

编辑:我已经使用以下方法进行了此工作:

STUFF(COALESCE(', ' + NULLIF(C1.[AddressLine1], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[AddressLine2], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[City], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[State], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[Country], ''), '') + 
  COALESCE(', ' + NULLIF(C1.[ZipCode], ''), ''),1, 1, '') AS 'Primary Address'

Answer 1

您可以尝试 PIVOT TABLE或 RECURSIVE CTE 。如果您知道用户将拥有多少个地址，则数据透视表将起作用(但是，如果您知道您也可能对其进行硬编码:))，CTE 将适用于未知数量的地址，但是，它不会创建多个列，但改为逗号分隔的列表。

无论采用哪种方式，您可能都需要使用 ROW_NUMBER创建唯一性顺序。

以下是递归 CTE 的工作示例:

SELECT *, ROW_NUMBER() OVER(PARTITION BY bioid ORDER BY contactid) AS rownum
INTO #TableToRecurse
FROMContactDetails

;WITH FinalOutput AS
(
--Anchor row of recursion
SELECT bioid, firstname, lastname, addressline, city, rownum
FROM #TableToRecurse 
WHERE rownum = 1
UNION ALL 
--Recursion piece
SELECT tr.bioid, tr.firstname, tr.lastname, 
    FinalOutput.addressline + ', ' + tr.addressline, FinalOutput.city + ', ' + tr.city, tr.rownum
FROM #TableToRecurse AS tr
    JOIN FinalOutput
        ON  FinalOutput.bioid = tr.bioid AND FinalOutput.rownum = tr.rownum +1
)
--Final output only showing the last row (Max)
--Which should have everything concatenated together
SELECT FinalOutput.bioid, FinalOutput.firstname, FinalOutput.lastname, 
    FinalOutput.addressline, FinalOutput.city
FROM FinalOutput 
    JOIN 
    (
        SELECT MAX(rownum) AS MaxRowNum, bioid
        FROM FinalOutput
        GROUP BY bioid
    ) AS MaxForEach
        ON FinalOutput.bioid = MaxForEach.bioid 
            AND FinalOutput.rownum = MaxForEach.MaxRowNum