SQL Server：如何将多个行值分组到单独的列中？

Question

我试图创建一个SELECT查询，使我可以将联系人的多个电话号码合并到单独的列中。

例如，我想要以下内容

Email  |  Phone  |  ID  |
------------------------|
[e-mail]| 555-5555| 001  |
[e-mail]| 555-5556| 001  |

相反是：

Email  |  Phone  |  Phone2  | Phone3 |  ID  |
--------------------------------------------|
[e-mail]| 555-5555| 555-5556 |  NULL  | 001  |

尽管这可能是不好的做法，但严格来说，我可以将结果导出到CSV，以便将该数据上传到CRM系统，该系统在导入时不允许任何重复。从那以后MIN，我四处张望，似乎找不到与电话号码相关的答案，并且MAX只允许输入两个而已。

最复杂的部分是数据库的结构，最有可能涉及复杂的查询。这是到目前为止我尝试过的操作，但是只能获得2个数字，并且我需要其余的列（请记住，我不是SQL专家）：

SELECT DISTINCT
       C.CONTACTID,
       MAX(T.NUMBERVALUE) AS Phone1,
       MIN(T.numbervalue) AS Phone2
FROM TBL_PHONE T
     JOIN TBL_CONTACT C ON C.CONTACTID = T.CONTACTID
     JOIN TBL_EMAIL E ON E.CONTACTID = C.CONTACTID
WHERE T.NUMBERVALUE IS NOT NULL
      AND LEN(T.NUMBERVALUE) > 0
GROUP BY C.CONTACTID;

Answer 1

这是一种动态枢轴方法：

declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
insert into @table
values

('[e-mail]','555-5555','001'),
('[e-mail]','555-5556','001'),
('[e-mail]','555-5557','001'),
('[e-mail]','555-5558','001'),
('[e-mail]','333-5556','002'),
('[e-mail]','444-5556','002'),
('[e-mail]','777-5556','002')


select
    Email
    ,Phone
    ,ID
    ,row_number() over (partition by ID order by Phone) as RN
into #staging
from 
    @table




DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(RN)
FROM (SELECT DISTINCT RN FROM #staging) AS RN

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Email, ID, ' + @ColumnName + '
    FROM #staging
    PIVOT(MAX(Phone) 
          FOR RN IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

drop table #staging

如前所述，如果您只期望3，则可以跳过动态…

declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
insert into @table
values

('[e-mail]','555-5555','001'),
('[e-mail]','555-5556','001'),
('[e-mail]','333-5556','002'),
('[e-mail]','444-5556','002'),
('[e-mail]','777-5556','002')

;with cte as(
select
    Email
    ,Phone
    ,ID
    ,row_number() over (partition by ID order by Phone) as RN
from 
    @table)

select
    Email
    ,max(case when RN = 1 then Phone end) as Phone1
    ,max(case when RN = 2 then Phone end) as Phone2
    ,max(case when RN = 3 then Phone end) as Phone3
    ,ID
from
    cte
group by
    Email
    ,ID