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.', NU开发者_如何学运维LL

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())

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

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.

The resultant string should be like following

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

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

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

Thanks.

You can concatenate without the loop via something like below; just add in your own query / joins etc as necessary:

DECLARE @s varchar(max) = ''

SELECT @s = @s + '<br/><b>' + CONVERT(varchar(10), i.CreatedDate, 101) + '</b>' + ISNULL(i.Notes, '')
FROM @Items i

SELECT @s

(you must make sure there are no NULLs in there)

but do not do this!

The database is not the place to build html; not least, it opens you up hugely to XSRF vulnerabilities. I would do this in the UI layer, making appropriate use of the html-encoding functions provided by whichever platform you are using. Blindly concatenating strings as html is pretty much on a par with blindly concatenating user strings into TSQL (instead of using parameters); at best the formatting will be borked (no correct handling of <, etc) - at worst you place your users at direct risk of attack.

How about something like

;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