将存储过程中的行连接到 TSQL 中的另一个表 [英] Concatenating rows from a stored procedure onto another table in TSQL

问题描述

我有一个表格，其中在电子邮件字段中包含许多 NULL.我需要做的是更新所有 NULL 行.对于每个 NULL 行，我需要执行一个存储过程，该过程基本上获取与该用户 ID 相关的所有电子邮件.因此，对于每个 NULL 行，都会调用此存储过程，并且需要将在此存储过程中找到的所有电子邮件字段连接在一起，并插入其他表中的 NULL 电子邮件字段.

I have a table which contains lots of NULLs in the email field. What I need to do is updating all the NULL rows. For every NULL row, I need to execture a stored procedure which basically gets all the emails related to that user's ID. Therefore, for every NULL row, this stored procedure is called and all the email fields found within this stored procedure need to be concatenated together and inserted in place of the NULL email field from the other table.

有人知道如何在 TSQL 中实现吗?

Anyone knows how to implement this in TSQL?

非常感谢

我找到了这个代码:

WITH Ranked ( CategoryId, rnk, ProductName )  
             AS ( SELECT CategoryId,
                         ROW_NUMBER() OVER( PARTITION BY CategoryId ORDER BY CategoryId ),
                         CAST( ProductName AS VARCHAR(8000) ) 
                    FROM Northwind..Products),
   AnchorRanked ( CategoryId, rnk, ProductName )  
             AS ( SELECT CategoryId, rnk, ProductName 
                    FROM Ranked
                   WHERE rnk = 1 ),
    RecurRanked ( CategoryId, rnk, ProductName ) 
             AS ( SELECT CategoryId, rnk, ProductName 
                    FROM AnchorRanked
                   UNION ALL 
                  SELECT Ranked.CategoryId, Ranked.rnk,
                         RecurRanked.ProductName + ', ' + Ranked.ProductName
                    FROM Ranked
                   INNER JOIN RecurRanked 
                      ON Ranked.CategoryId = RecurRanked.CategoryId 
                     AND Ranked.rnk = RecurRanked.rnk + 1 )
    SELECT CategoryId, MAX( ProductName ) 
      FROM RecurRanked
     GROUP BY CategoryId;

但是我无法让它在我的情况下工作.

However I can't get it to work in my case.

基本上，我可以简单地使用 select 语句来获取包含所有必要电子邮件的一行，而不是使用存储过程.我基本上需要做的是将这些返回的电子邮件连接成一行.

Basically, instead of using the stored procedure I can simply use the select statement to get one row with all the necessary emails. What I essentially need to do is to concatenate these returned emails into one row.

推荐答案

来自:http://geekswithblogs.net/nagendraprasad/archive/2009/03/13/convert-multiple-rows-into-one-row---sql-server.aspx

将多行转换为一行 - SQL Server由于我需要向很多人发送电子邮件，因此我需要将多封电子邮件转换为由分号(;)分隔的单行，我有很多解决方案，但这是一种需要更多代码行的旧解决方案.由于我想使用可以解决的一两行代码，因此我找到了三种非常简单的解决方案.

Convert multiple rows into one row - SQL Server As I need to send email to many people, i need to convert multiple emails into a single row delimited by semi-colon(;), i had lots of solutions, but which is an old type of solution which needs more lines of code. As i want to use one or two line code which would resolve, i found three methods for my solution which is very simple.

方法一:

DECLARE @str varchar(4000)
SET @str = (SELECT CONTACT_EMAIL + ';' FROM table FOR XML PATH(''))
SET @str = SUBSTRING(@str,1,LEN(@str)-1)
SELECT @str

方法二:

DECLARE @str varchar(4000)
SELECT @str = COALESCE(@str + ';', '') + CONTACT_EMAIL FROM table 
SELECT @str

方法三:

DECLARE @str varchar(4000)
SELECT DISTINCT STUFF( (SELECT CONTACT_EMAIL + ';'   from table FOR XML PATH('')),1,1,'')
SELECT @str

返回多行:

CONTACT_EMAIL
[e-mail]
[e-mail]
[e-mail]

3 行受到影响.

执行其中一种方法后，我得到的结果为

After executing one of the methods, i got the result as

CONTACT_EMAIL

CONTACT_EMAIL

[e-mail];[e-mail];[e-mail];

1 行受到影响.

这篇关于将存储过程中的行连接到 TSQL 中的另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！