获取逗号分隔值并以逗号分隔值更新 [英] Fetch comma seperated value and update as comma seperate value
问题描述
我有两张桌子。表1，表2。我必须根据Table1和Table2中常见的其他id获取Table2中的名称。我必须更新表1，字段名称是"名称"
表1
ID1，Name1，ID2，Name2，ID3，名称3 - 表1中的字段
ID1, Name1, ID2, Name2, ID3, Name3 - fields in Table1
id1 | Name1
-------------
a,b,c |
e，f |
表
id | Name
------------
a | dinesh
b | suresh
c | ganesh
e |约翰
f | ken
表1中的输出
ID1，Name1，ID2，Name2，ID3， Name3 - Table1中的字段
ID1, Name1, ID2, Name2, ID3, Name3 - fields in Table1
Id1 | Name1 | Id2 | Name2| Id3 | Name4
-------------------------------
a,b,c | dinesh, suresh, ganesh
e，f | john，ken
e,f | john, ken
推荐答案
这是表示数据的理想方法
This is not ideal way to represent data in your tables
理想情况下，您应该规范化表1并确保每行存储一个ID。否则，您的数据模型违反了第一范式
Ideally you should normalize table 1 and make sure you store one id per row. Otherwise your data model is violating First Normal Form
无论如何，在当前形状中，您需要一个如下逻辑
Anyways in the current shape, you woould require a logic like below
;With CTE
AS
(
SELECT t1.id,f.Value,t2.Name,ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t1.id) AS Seq
FROM table1 t1
CROSS APPLY String_Split(t1.id,',') f
INNER JOIN Table2 t2
ON t2.id = f.Value
)
UPDATE t
SET Name = c.Names
FROM Table1 t
JOIN
(
SELECT id,
STUFF((SELECT ',' + Name
FROM CTE
WHERE id = c.id
ORDER BY Seq
FOR XML PATH('')),1,1,'') AS Names
FROM CTE c
GROUP BY id
)c
ON c.id = t.id如果您的版本低于SQL 2016，则需要字符串解析UDF，因为String_Split函数不可用
if your version is below SQL 2016 you would need a string parsing UDF as String_Split function is not available
在这种情况下使用UDF如下所示
In that case use a UDF like below
https://visakhm.blogspot.com/2010/02/parsing-delimited-string.html
并制定解决方案，如
;With CTE
AS
(
SELECT t1.id,f.Val,t2.Name,f.ID AS Seq
FROM table1 t1
CROSS APPLY dbo.ParseValues(t1.id,',') f
INNER JOIN Table2 t2
ON t2.id = f.Val
)
UPDATE t
SET Name = c.Names
FROM Table1 t
JOIN
(
SELECT id,
STUFF((SELECT ',' + Name
FROM CTE
WHERE id = c.id
ORDER BY Seq
FOR XML PATH('')),1,1,'') AS Names
FROM CTE c
GROUP BY id
)c
ON c.id = t.id
这篇关于获取逗号分隔值并以逗号分隔值更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
