How to concatenate a column when data is contained over multiple rows

Question

Hello, 

I have a task that I have been struggling with, so hopefully someone can help me out with this one.  I have a table that I'm querying which contains customer record notes, but my issue is that one note can span multiple rows, so I have data that looks like this:

So in this case, the first 3 rows are the same note, the same for rows 5 and 6.  The sequence # tells me that it's another record for the same note.  What I am trying to do is combine them so that there will only be 1 row for each note, so I want the result to look like this:

After reviewing some other posts on the forum, I have been able to get the notes into multiple columns in the same row (for those that span over multiple rows), but I'd like to get the notes into the 1 column if it's possible.

If anyone can help I would greatly appreciate it.

Thank you!

Answer 1

see the e.g bellow

CREATE TABLE #tempCityState (nd int, nt int ,ns int,n  VARCHAR(50))

INSERT INTO #tempCityState
SELECT 201020,1233,1, 'Denver' UNION
SELECT 201020,1233,2, 'Teluride' UNION
SELECT 201020,1233,3, 'Vail' UNION
SELECT 201021,12335 ,1, 'Aspen' UNION
SELECT 201021,12335 ,2, 'Los Anggeles' 
select * from #tempCityState



SELECT DISTINCT State, (SELECT STUFF((SELECT ',' + City
 FROM #tempCityState
 WHERE State  = t.State
 FOR XML PATH('')),1,1,'')) AS Cities
FROM #tempCityState t

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

/* You can generally use the technique vt gave.
So for example with your data */
Declare @MyTable Table(note_date int, note_time int, note_seq int, note varchar(100));
Insert @MyTable(note_date, note_time, note_seq, note) Values
(20120820, 92557, 1, 'Called customer and said '),
(20120820, 92557, 2, 'that they could make a payment '),
(20120820, 92557, 3, 'as soon as they open tomorrow'),
(20120820, 92239, 1, 'Left a voice mail for Eric to call me back'),
(20120814, 93350, 1, 'I recieved a letter from the customer stat'),
(20120814, 93350, 2, 'ing that they received our letter yesterday');

/* Then you would do */
Select Distinct m.note_date, m.note_time, 
  (Select '' +  m2.note
   From @MyTable m2
   Where m.note_date = m2.note_date And m.note_time = m2.note_time
   Order By m2.note_seq
   For XML Path('')) As note  
From @MyTable m;

/* And that works fine unless you have one of the XML
special characters, they are " ' & < >
If you have any of those 5 characters, they will be
converted, for example < will become &lt;
So lets add some additional rows and see what we get */

Insert @MyTable(note_date, note_time, note_seq, note) Values
(20120827, 92500, 1, 'Called customer & said '),
(20120827, 92500, 2, 'that they must make a payment in < 3 days');

Select Distinct m.note_date, m.note_time, 
  (Select '' +  m2.note
   From @MyTable m2
   Where m.note_date = m2.note_date And m.note_time = m2.note_time
   Order By m2.note_seq
   For XML Path('')) As note  
From @MyTable m;

/* If those conversions are acceptable in your environment
or if you will never use those characters then the above
query will work for you.
But if you need to have those characters show up correctly in your result
you need a more complex query using the XML TYPE directive
So then you would do */

;With cte As
(Select m.note_date, m.note_time, 
  (Select '' +  m2.note As [text()]
   From @MyTable m2
   Where m.note_date = m2.note_date And m.note_time = m2.note_time
   Order By m2.note_seq
   For XML Path(''), Type) As note  
From @MyTable m)
Select Distinct note_date, note_time, note.value('.', 'varchar(max)') As note 
From cte;

Tom

Answer 3

Declare @MyTable Table(note_date int, note_time int, note_seq int, note varchar(100));
Insert @MyTable(note_date, note_time, note_seq, note) Values
(20120820, 92557, 1, 'Called customer and said '),
(20120820, 92557, 2, 'that they could make a payment '),
(20120820, 92557, 3, 'as soon as they open tomorrow'),
(20120820, 92239, 1, 'Left a voice mail for Eric to call me back'),
(20120814, 93350, 1, 'I recieved a letter from the customer stat'),
(20120814, 93350, 2, 'ing that they received our letter yesterday');
Select Main.note_time,
       Main.Note As Notee
From(

Select distinct m2.note_time, 
           (
Select m1.note + '' AS [text()]
            From @MyTable m1
            Where m1.note_time = m2.note_time
            ORDER BY m1.note_time
            For XML PATH ('')
            ) Note
     From @MyTable m2
     ) [Main]

Answer 4

I tried this out and I think it is going to work perfectly....thank you for the help!