Concatenate field values based another row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Concatenate field values based another row

Author

Topic

shelbycrawford
Starting Member

3 Posts

Posted - 2012-03-12 : 11:50:56

I have a table that came from a delimited file but was brought in wrong. The notes column ended up on multiple rows but should have been brought in keyed on the date field. Is it possible to concatenate the notes columns and update the row that the complete note should be on? Example -- in the table below, the row with date 8/2/2009 should have the notes text from row ID 1, 2 and 3. Instead, the notes were split up and ended on the next 2 rows as well.
I don't have access to the original file.


ID   Date	    Notes
-------------------------------------------------	
1    8/2/2009       called and left message
2    null           at 4:30 pm. Will call 
3    null           back on 8/5/2009
4    9/4/2004       received confirmation on
5    null           application.  Applicant 
6    null           will call back on 9/6/2004
7    null           to follow up.
8    11/25/2007     no call back. -sdc
9    6/30/2010      application accepted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 12:31:38

[code]
UPDATE t
SET t.Notes = t.Notes + STUFF((SELECT ' ' + Notes FROM table WHERE ID > t.ID AND ID < t1.ID ORDER BY ID FOR XML PATH('')),1,1,'')
FROM table t
CROSS APPLY (SELECT TOP 1 ID
FROM table
WHERE ID > t.ID
AND Date IS NOT NULL
ORDER BY ID)t1
WHERE t.Date IS NOT NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shelbycrawford
Starting Member

3 Posts

Posted - 2012-03-14 : 12:21:55

Thanks visakh16. That worked.

Here is my next challange. Now that all the notes are fixed, I need to align customer records with multiple notes. I need to update the customer field with there name on the other rows that contain their notes where their name is missing. This is how the table looks. Any idea?


ID     Customer          Date             Notes
----------------------------------------------------------------------
26     ABC Constr.       8/26/2008        SC - Called and left VM
27                       8/30/2008        SC - no answer no vm
28                       9/6/2008         TR - Called in.  Apply cr.
29     Aberson, inc.     2/2/2009         LP - Paperwork complete
30     Anderson & son    4/2/2009         SO - Have to credit acct.
31                       4/6/2009         SO - Credited account
32                       4/20/2009        LP - Sent application

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 12:48:18

[code]
UPDATE t
SET t.Customer = t1.Customer
FROM table t
CROSS APPLY (SELECT TOP 1 Customer
FROM table
WHERE ID < t.ID
AND Customer IS NOT NULL
ORDER BY ID DESC)t1
WHERE t.Customer IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shelbycrawford
Starting Member

3 Posts

Posted - 2012-03-14 : 14:20:49

Thanks. I will try this. One thing on the first update. Any row that has the # symbol in it is being updated to null. Is this because the update statement is using the "for xml path" condition?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 15:46:22

# symbol on which field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources