我需要为与客户关联的所有行分配一个新的"责任"列的名称。
如果"codes"中字符串的一部分由"manager"组成,则应将manager的名称分配给"responsible"列。如果代码列中没有"manager",则应使用与该行关联的"empl_name"填充"responsible"列。
我认为应该使用案例和分组依据?
表格看起来像:
cust_name empl_name codes
john mike empl, office
liza nick manager_1, remote
john kate empl, remote
john mike empl, remote
liza mike empl, office
david kate empl, remote
john mike empl, remote
liza mike empl, office
david mike empl, remote
chris jennifer manager_2, office
输出应该是:
cust_name empl_name codes responsible
john mike empl, office mike
liza nick manager_1, remote nick
john kate empl, remote kate
john mike empl, remote mike
liza mike empl, office nick
david kate empl, remote kate
john mike empl, remote mike
liza mike empl, office nick
david mike empl, remote mike
chris jennifer manager_2, office jennifer
我的代码(谷歌搜索了所有内容(:
SELECT
c.cust_name,
e.emp_name,
a.codes,
FROM Billing as b
--- Code Labels in 1 single row, separated by comma
OUTER APPLY (
SELECT STUFF((
(SELECT ', ' + y.CodeLabelName
FROM CodeToLabelBridge x
JOIN CodeLabel y
ON y.CodeLabelId = x.CodeLabelId
WHERE x.CodeId = b.billing_code_id
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')),1,1,''
) AS codes
) AS a
--- JOINS
JOIN Client as c
ON (b.billing_cust_id = c.cust_id)
JOIN Employer as e
ON (b.billing_emp_id = e.emp_id)
JOIN Code as sc
ON (b.billing_code_id = sc.codes_id)
--- Table with Client and associate Manager
WITH cte AS (
SELECT * ,
row_number() OVER(PARTITION BY t.cust_name, t.empl_name ORDER BY t.cust_name desc) AS [rn]
FROM t
WHERE t.codes LIKE '%manager%'
)
Select cust_name, empl_name from cte WHERE [rn] = 1
然后我就被卡住了。我想在"cust_name"字段上加入cte表和main表,但有问题。
如果数据中为客户的每个联系人/代表都有一行,并显示经理(如果存在(,听起来你想知道谁是客户的"最终"负责人。这个(假设你的表是Tbl(可以做到:
select
a.*,
Responsible=coalesce((select min(b.empl_name)
from Tbl b
where a.cust_name=b.cust_name
and b.codes like '%manager%'), a.empl_name)
from Tbl a
我使用min((来避免在客户的代码中有多行带有"manager"时可能出现的错误。
如果管理器中没有其他记录,则合并使用当前行的empl_name;因为选择子查询将返回NULL。