我的数据集如下所示:
| LBID | ADDRESS | FI | LI |
|---|---|---|---|
| 101 | 7283 | JOHN | SMITH |
| 101 | 7283 | JANE | JONES |
由于John和Jane具有相同的LBID和相同的地址，因此我希望输出如下所示:
| LBID | ADDRESS | FI | LI | FI2 | LI2 |
|---|---|---|---|---|---|
| 101 | 7283 | JOHN | SMITH | JANE | JONES |
我在Stack Overflow上判断了类似的问题，但它们都处理具有Null的列.我的没有一个是空的.
我试了STUFF次、STRING_AGG次和JOIN次手术，但都不能达到我想要的效果.我最近的一次try 是:
SELECT LBID, ADDRESS, STRING_AGG(FI, '& ') AS FI, LI
FROM TABLENAME
GROUP BY LBID, ADDRESS, LI
但由于姓氏不同，它仍然将其放在两个不同的行.
你对如何做到这一点有什么 idea 吗？
如果您假设一个最多有两个记录重复的固定模式，则可以使用ROW_NUMBER窗口函数计算重复记录.因为它最多只能取值2(两个重复的值)，所以您可以使用典型的透视方法来解决这个问题，并为每一行提取相应的rownum.
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY LBID, ADDRESS ORDER BY FI) AS rn
FROM tab
)
SELECT LBID, ADDRESS,
MAX(CASE WHEN rn = 1 THEN FI END) AS FI1,
MAX(CASE WHEN rn = 1 THEN LI END) AS LI1,
MAX(CASE WHEN rn = 2 THEN FI END) AS FI2,
MAX(CASE WHEN rn = 2 THEN LI END) AS LI2
FROM cte
GROUP BY LBID, ADDRESS
查看演示here.
输出:
| LBID | ADDRESS | FI | LI | FI2 | LI2 |
|---|---|---|---|---|---|
| 101 | 7283 | JOHN | SMITH | JANE | JONES |
如果您有2条以上的重复记录，那么在最终的查询中添加更多的Pivotal元素就足够了，并提取耦合的"rn="，如下面的伪代码所示:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY LBID, ADDRESS ORDER BY FI) AS rn
FROM tab
)
SELECT LBID, ADDRESS,
MAX(CASE WHEN rn = 1 THEN FI END) AS FI1,
MAX(CASE WHEN rn = 1 THEN LI END) AS LI1,
MAX(CASE WHEN rn = 2 THEN FI END) AS FI2,
MAX(CASE WHEN rn = 2 THEN LI END) AS LI2,
...
...
MAX(CASE WHEN rn = <n> THEN FI END) AS FI<n>
MAX(CASE WHEN rn = <n> THEN LI END) AS LI<n>
FROM cte
GROUP BY LBID, ADDRESS
如果您不知道每个(LBID，地址)最多可以有多少重复记录，则可以运行查询以了解该值:
SELECT TOP(1) COUNT(1) AS cnt
FROM tab
GROUP BY LBID, ADDRESS
ORDER BY cnt DESC
该值将是要提取的几个关键元素的数量:
MAX(CASE WHEN rn = <n> THEN FI END) AS FI<n>
MAX(CASE WHEN rn = <n> THEN LI END) AS LI<n>
对于示例表的特定情况，为2(根据第一个查询).
如果每个地址有如此多的重复记录，并且想要一种通用的方法，那么您将转向动态查询.然而，这种方法的性能可能会较差，并且容易出现SQL注入.