循环表(不使用游标)连接数据 [英] Loop on table (Without using cursor) to concate data

问题描述

请查看/运行以下脚本.

Please see/run the following script.

DECLARE @Products Table (   PId int )

INSERT @Products SELECT 100
INSERT @Products SELECT 101
INSERT @Products SELECT 102

DECLARE @Stff Table ( Stid int, StaffName VARCHAR(20) )

INSERT @Stff SELECT 301, 'White'
INSERT @Stff SELECT 302, 'Green'

DECLARE @Items Table
(
    ItemId int,
    PIdFK int,
    StIdFK int,
    CreatedDate DateTime,
    Notes varchar(100),
    NotesHistory varchar(2000)
)

INSERT INTO @Items SELECT 1, 100, 301, GETDATE(), Null, NULL
INSERT INTO @Items SELECT 2, 101, 301, GETDATE(), Null, NULL
INSERT INTO @Items SELECT 3, 101, 301, DATEADD(DD, 1, GETDATE()), 'Hello Dear', NULL
INSERT INTO @Items SELECT 4, 101, 301, DATEADD(DD, 2, GETDATE()), 'Did you get my msg?', NULL
INSERT INTO @Items SELECT 5, 102, 302, GETDATE(), Null, NULL
INSERT INTO @Items SELECT 6, 102, 302, DATEADD(DD, 1, GETDATE()), Null, NULL
INSERT INTO @Items SELECT 7, 102, 302, DATEADD(DD, 3, GETDATE()), 'How are you doing?', NULL
INSERT INTO @Items SELECT 8, 102, 302, DATEADD(DD, 4, GETDATE()), 'Your Items are ready.', NULL

SELECT * FROM @Items

DECLARE @ItemsHistory Table
(
    ItemHisotryId int,
    ItemIdFK int,
    StIdFK int,
    NotesHisotry varchar(200),
    CreatedDate DATETIME
)

INSERT INTO @ItemsHistory SELECT 1, 1, 301, NULL, GETDATE()
INSERT INTO @ItemsHistory SELECT 2, 2, 301, NULL, GETDATE()
INSERT INTO @ItemsHistory SELECT 3, 3, 301, 'Hello Dear', DATEADD(DD, 1, GETDATE())
INSERT INTO @ItemsHistory SELECT 4, 4, 301, 'Dimd you get my msg?', DATEADD(DD, 2, GETDATE())
INSERT INTO @ItemsHistory SELECT 5, 5, 302, NULL, GETDATE()
INSERT INTO @ItemsHistory SELECT 6, 6, 302, NULL, DATEADD(DD, 1, GETDATE())
INSERT INTO @ItemsHistory SELECT 7, 7, 302, 'How are you doing?', DATEADD(DD, 3, GETDATE())
INSERT INTO @ItemsHistory SELECT 8, 8, 302, 'Your Items are ready', DATEADD(DD, 4, GETDATE())

我想将 Items 表的 Notes 列和 ItemsHistory 表的 NotsHistory 列中的所有数据合并为一个字符串，实际上我想更新 Items.NotesHistory Items 列与所有 ItemsHistory.NotesHisotry 列相关的 PID

I want to concat all data in Notes column of Items table and NotsHistory column of ItemsHistory table into one string, In-fact I want to update Items.NotesHistory column of Items table with all ItemsHistory.NotesHisotry column related on PID

你能在不使用光标的情况下帮助解决这个问题吗?我正在使用 sql server 2008，所以 CTE 可以做到这一点，但我不知道如何实现这一点.

Can you please help this without using cursor. I am using sql server 2008 so CTE can do this but I am not getting that how I can achieve this.

结果字符串应如下所示

<b>Items.CreaedDate - Items.StaffName: <b/> Items.Notes <br/><b>ItemsHisotryCreatedDate - ItemsHistory.StaffName <b> ItemsHistory.NotesHistory

以下部分将从 ItemsHistory 表中添加到为唯一PId"添加的每条记录

Following part will be added from ItemsHistory table on each record added for unique 'PId'

<br/><b>ItemsHisotryCreatedDate - ItemsHistory.StaffName <b> ItemsHistory.NotesHistory

谢谢.

推荐答案

怎么样

;WITH Vals AS (
        SELECT  PIdFK,
                Notes,
                ItemIdFK
        FROM    @ItemsHistory ih INNER JOIN
                @Items i    ON  ih.ItemIdFK = i.ItemId
        WHERE   Notes IS NOT NULL
)
SELECT 
        v.PIdFK,
        STUFF( 
               (SELECT 
                    ', ' + v2.Notes 
                    FROM Vals v2 
                    WHERE v.PIdFK=v2.PIdFK 
                    ORDER BY v2.ItemIdFK 
                    FOR XML PATH(''), TYPE 
               ).value('.','varchar(max)') 
               ,1,2, '' 
          ) AS Notes 
FROM Vals v 
GROUP BY v.PIdFK

这篇关于循环表(不使用游标)连接数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！