SQL Server:如何将多个行值分组到单独的列中? [英] SQL Server: How can I group multiple row values into separate columns?
问题描述
我正在尝试创建一个 SELECT 查询，它允许我将一个联系人的多个电话号码组合到单独的列中.
I am attempting to create a SELECT query that will allow me to combine multiple phone numbers for a Contact into separate columns.
例如，我想要以下
Email | Phone | ID |
------------------------|
x@x.com| 555-5555| 001 |
x@x.com| 555-5556| 001 |
改为:
Email | Phone | Phone2 | Phone3 | ID |
--------------------------------------------|
x@x.com| 555-5555| 555-5556 | NULL | 001 |
虽然这可能是不好的做法，但严格来说，我可以将结果导出到 CSV，以便将该数据上传到 CRM 系统中，该系统在导入时不允许任何重复.我已经看遍了，似乎找不到处理电话号码的答案，因为 MIN 和 MAX 只允许两个而不是更多.
Although this may be bad practice, it is strictly so I can export the results to a CSV in order to upload that data into a CRM system which does not allow any duplicates when importing. I have looked all over and cannot seem to find an answer that deals with phone numbers since MIN and MAX will only allow for two and no more.
最复杂的部分是数据库的结构，很可能涉及复杂的查询.这是我到目前为止所尝试的，但只能得到 2 个数字，我需要剩余的列(请记住，我不是 SQL 专家):
The most complicated part is how the database is structured and will most likely involve a complicated query. Here's what I tried so far but can only get 2 numbers and I need the remaining columns (please keep in mind, I'm not a SQL guy):
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;
推荐答案
这里是动态枢轴方式:
declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
insert into @table
values
('x@x.com','555-5555','001'),
('x@x.com','555-5556','001'),
('x@x.com','555-5557','001'),
('x@x.com','555-5558','001'),
('x@xdd.com','333-5556','002'),
('x@xdd.com','444-5556','002'),
('x@xdd.com','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
('x@x.com','555-5555','001'),
('x@x.com','555-5556','001'),
('x@xdd.com','333-5556','002'),
('x@xdd.com','444-5556','002'),
('x@xdd.com','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
这篇关于SQL Server:如何将多个行值分组到单独的列中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
