Category: SQL Server

31 Aug 2018 13:44

Joined:	27/01/2008
Level:	Gold
Points:	765

Points:	5

Append 2nd row Name value in 1st row Content column

Pls. help me to solve the below
I have the temp table as below:

S.No Name ID Content
1 test1 123 Successfully received test1 mail
2 test2 123 Successfully received test2 mail
3 test3 789 Successfully received test3 mail

Since the 1st and 2nd row has same ID value, i need to append the 2nd row name in 1st row's content column. i.e., output should be as below

S.No Name ID Content
1 test1 123 Successfully received test1, test2 mail
2 test2 123 Successfully received test2 mail ---------This should be deleted
3 test3 789 Successfully received test3 mail

#769744

01 Sep 2018 14:15

Joined:	06/04/2012
Level:	Gold
Points:	4215

Points:	3

Hi Kumaresh,

You need to store the last value of row in variables and check while reading next row. By doing comparison you can achieve it.

However you can also achieve it by this way:
SELECT t1.SNo, t1.Name, t1.ID
Contents = REPLACE( (SELECT Content AS [data()]
FROM mytable t2
WHERE t2.ID = t1.ID
ORDER BY Content
FOR XML PATH('')
), ' ', ',')
FROM mytable t1
GROUP BY ID ;

I hope this will help you.

Regards,
Nirav Lalan
DNS Gold Member
"Failure is the path of least persistence"

#769747

07 Sep 2018 12:09

Joined:	07/09/2018
Level:	Bronze
Points:	0

Points:	3

Thank you Nirav. Great material! This is really a good message because you always post grand related content and very informative information with powerful points. Just like the article I was looking for to read the article I found on your website. Thanks for sharing this piece of writing on this website. I want to tell you that it is very helpful for us. Thanks for sharing such an awesome informatiopn with us. https://panseva.com/ I want to visit again.

Append 2nd row Name value in 1st row Content column

Email subscription

About dotnetspider.com

Quick Links

General

Services