469,280 Members | 2,450 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,280 developers. It's quick & easy.

How do I combine multiple rows into one row sql?

I need to take any amount of rows and turn them into one single row. I'm exporting some data from sql. The program its going into only has one note field. I've used UNION and those types of functions, not sure what to do this time. This is SQL server and will be part of a stored procedure.

example data:

NOTES NOTE TIME
yayayay hey yo 4/25/2006
blah blah blaaah 5/1/2006
second note blah 5/2/2006

Into One column, One row

NOTE
4/25/2006: yayayay hey yo 5/2/2006: blah blah blaaah 5/2/2006: second note blah
May 2 '06 #1
4 46018
CaptainD
135 100+
you'll have to do it by query with the fields joined as one with "+" signs
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO YourTable (yourColumn) SELECT Column1 + Column2 + ColumnEtc AS OnePieceOfData FROM TheOtherTable
  2.  
May 2 '06 #2
you'll have to do it by query with the fields joined as one with "+" signs
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO YourTable (yourColumn) SELECT Column1 + Column2 + ColumnEtc AS OnePieceOfData FROM TheOtherTable
  2.  
I'm a little further along, and what you've said isn't what I'm looking for.

Basically I need to take every note one of our users may or may not have left and combine that and some other fields into one giant text field so it can be exported to a new program the lawyers are using.


So I thought I could cursor through the notes combing as I go and came up with this:


Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE prcCombineInquiryNotes (@inquiry_id int, 
  2. @massive_note varchar(8000) OUTPUT
  3. )
  4. AS
  5.  
  6. --these hold what's in the select statement each go around
  7. DECLARE @username varchar(50)
  8. DECLARE @notes varchar(500)
  9. DECLARE @noteDate datetime
  10.  
  11.  
  12. DECLARE curInqNotes CURSOR FOR
  13.  
  14. SELECT    inquiry_notes.notes, CONVERT(VARCHAR, inquiry_notes.note_date_time, 101) AS noteDate, administrator.username
  15. FROM      inquiry_notes INNER JOIN
  16.           administrator ON inquiry_notes.admin_id = administrator.member_id
  17. WHERE     (inquiry_notes.inquiry_id = @inquiry_id)
  18.  
  19. -- open cursor
  20. OPEN curInqNotes
  21.  
  22.     -- Perform the first fetch.
  23.     FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
  24.  
  25.     -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
  26.     WHILE @@FETCH_STATUS = 0
  27.         BEGIN
  28.             -- Combines multiple notes into one note
  29.  
  30.         select @massive_note = 'Note: ' + @notes + CHAR(10)
  31.         select @massive_note = @massive_note + 'Left by: ' + @username + ' On: ' + CONVERT(VARCHAR, @noteDate, 101) + CHAR(10)
  32.  
  33.            -- This is executed as long as the previous fetch succeeds.
  34.         FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
  35.         END
  36.  
  37. CLOSE curInqNotes
  38. DEALLOCATE curInqNotes
  39.  
  40. RETURN
  41. GO
  42.  

The problem is I can't figure out how to combine the above procedure with the notes combined, with the other procedure that will be pulling the main data.


Expand|Select|Wrap|Line Numbers
  1. SELECT     IC.last_name, IC.first_name, IC.first_name + ' ' + IC.last_name AS full_name, IC.street, IC.suite, IC.postal_code, IC.city, IC.state_code, 
  2.                       IC.email_address, IC.home_phone, RTRIM(CONVERT(CHAR, I.inquiry_date, 101)) AS OpenDate, LKIT.inquiry_type AS ReferredBy, 
  3.                       'Inquiry ID#: ' + CONVERT(VARCHAR, I.inquiry_id) + ' Subject: ' + I.inquiry_subject + CONVERT(VARCHAR(3000), I.inquiry) AS Notes, 'NC' AS Class, 
  4.                       'LK' AS Attorney
  5.  
  6. FROM         dbo.inquiry I INNER JOIN
  7.                       dbo.inquiry_contact IC ON I.inquiry_contact_id = IC.inquiry_contact_id INNER JOIN
  8.                       dbo.lk_inquiry_type LKIT ON I.inquiry_type_id = LKIT.inquiry_type_id
  9. WHERE     (I.division_id = 5) AND (I.inquiry_status_id = 3)
  10.  
See I need the notes from prcCombineInquiryNotes by each record in the above query to go into the Notes column in the above query. I thought maybe I could do two cursors one inside the other but that didn't work out.

Anyone have any ideas?
May 3 '06 #3
CaptainD
135 100+
Basically I need to take every note one of our users may or may not have left and combine that and some other fields into one giant text field so it can be exported to a new program the lawyers are using.
Ok, I understand that now, since your exporting for another program, would (assuming MS SQL Server) the BCP Export feature do what you want to do?
May 3 '06 #4
vj1001
1
I'm a little further along, and what you've said isn't what I'm looking for.

Basically I need to take every note one of our users may or may not have left and combine that and some other fields into one giant text field so it can be exported to a new program the lawyers are using.


So I thought I could cursor through the notes combing as I go and came up with this:


Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE prcCombineInquiryNotes (@inquiry_id int, 
  2. @massive_note varchar(8000) OUTPUT
  3. )
  4. AS
  5.  
  6. --these hold what's in the select statement each go around
  7. DECLARE @username varchar(50)
  8. DECLARE @notes varchar(500)
  9. DECLARE @noteDate datetime
  10.  
  11.  
  12. DECLARE curInqNotes CURSOR FOR
  13.  
  14. SELECT    inquiry_notes.notes, CONVERT(VARCHAR, inquiry_notes.note_date_time, 101) AS noteDate, administrator.username
  15. FROM      inquiry_notes INNER JOIN
  16.           administrator ON inquiry_notes.admin_id = administrator.member_id
  17. WHERE     (inquiry_notes.inquiry_id = @inquiry_id)
  18.  
  19. -- open cursor
  20. OPEN curInqNotes
  21.  
  22.     -- Perform the first fetch.
  23.     FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
  24.  
  25.     -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
  26.     WHILE @@FETCH_STATUS = 0
  27.         BEGIN
  28.             -- Combines multiple notes into one note
  29.  
  30.         select @massive_note = 'Note: ' + @notes + CHAR(10)
  31.         select @massive_note = @massive_note + 'Left by: ' + @username + ' On: ' + CONVERT(VARCHAR, @noteDate, 101) + CHAR(10)
  32.  
  33.            -- This is executed as long as the previous fetch succeeds.
  34.         FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
  35.         END
  36.  
  37. CLOSE curInqNotes
  38. DEALLOCATE curInqNotes
  39.  
  40. RETURN
  41. GO
  42.  

The problem is I can't figure out how to combine the above procedure with the notes combined, with the other procedure that will be pulling the main data.


Expand|Select|Wrap|Line Numbers
  1. SELECT     IC.last_name, IC.first_name, IC.first_name + ' ' + IC.last_name AS full_name, IC.street, IC.suite, IC.postal_code, IC.city, IC.state_code, 
  2.                       IC.email_address, IC.home_phone, RTRIM(CONVERT(CHAR, I.inquiry_date, 101)) AS OpenDate, LKIT.inquiry_type AS ReferredBy, 
  3.                       'Inquiry ID#: ' + CONVERT(VARCHAR, I.inquiry_id) + ' Subject: ' + I.inquiry_subject + CONVERT(VARCHAR(3000), I.inquiry) AS Notes, 'NC' AS Class, 
  4.                       'LK' AS Attorney
  5.  
  6. FROM         dbo.inquiry I INNER JOIN
  7.                       dbo.inquiry_contact IC ON I.inquiry_contact_id = IC.inquiry_contact_id INNER JOIN
  8.                       dbo.lk_inquiry_type LKIT ON I.inquiry_type_id = LKIT.inquiry_type_id
  9. WHERE     (I.division_id = 5) AND (I.inquiry_status_id = 3)
  10.  
See I need the notes from prcCombineInquiryNotes by each record in the above query to go into the Notes column in the above query. I thought maybe I could do two cursors one inside the other but that didn't work out.

Anyone have any ideas?

http://vj-blogsql.blogspot.com/
May 8 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Antanas | last post: by
4 posts views Thread by musicloverlch | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.