SQL Server 大小写表示文本分成多列

Question

另一个Case表达式问题，我正在使用SQL Server 2016。

我正在尝试为每个具有多个客户列的属性返回 1 行。 如果属性有 1 个客户，则第 2 和第 3 个客户列应为 null。我使用同一表的联接向客户返回了针对该属性的RANK，但我的结果为每个属性返回多行。

期望输出：

Prop    Cust1   Cust2   Cust3
-------------------------------
PropA   Fred    John    NULL
PropB   Ang     Jo      Allan

查询：

SELECT DISTINCT
x.Property as Prop,
CASE WHEN x.RANK = 1 THEN x.Customer END AS Cust1,
CASE WHEN x.RANK = 2 THEN x.Customer END AS Cust2,
CASE WHEN x.RANK = 3 THEN x.Customer END AS Cust3
FROM 
tbl_CustbyProperty c
LEFT JOIN
(SELECT DISTINCT  
Cust_ID, Customer, Property
ROW_NUMBER() OVER (PARTITION BY c.Property ORDER BY c.customer) AS RANK
FROM 
tbl_CustbyProperty) x ON c.Cust_ID = x.Cust_ID
ORDER BY 
y x.Property

电流输出：

Prop     Cust1   Cust2   Cust3
--------------------------------
PropA    Fred    NULL    NULL
PropA    NULL    John    NULL
PropB    Ang     NULL    NULL
PropB    NULL    Jo      NULL
PropB    NULL    NULL    Allan

Answer 1

要从当前结果转到所需的结果，您可以GROUP BY x.property并获得CASEs的max()。 类似于：

SELECT x.property prop,
max(CASE 
WHEN x.rank = 1 THEN
x.customer
END) cust1,
max(CASE
WHEN x.rank = 2 THEN
x.customer
END) cust2,
max(CASE
WHEN x.rank = 3 THEN
x.customer
END) cust3
FROM tbl_custbyproperty c
LEFT JOIN (SELECT DISTINCT
cust_id,
customer,
property,
row_number() OVER (PARTITION BY c.property
ORDER BY c.customer) rank
FROM tbl_custbyproperty) x
ON c.cust_id = x.cust_id
GROUP BY x.property
ORDER BY x.property;

但是，对于整个问题，可能有一个整体更好的解决方案，如果您编辑问题并发布表的CREATE语句以及您获得结果的示例数据的INSERT语句，您可能会得到该解决方案。