How do I combine multiple rows into one row sql?

[Iprogramforlawyers]

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

[CaptainD]

you'll have to do it by query with the fields joined as one with "+" signs

INSERT INTO YourTable (yourColumn) SELECT Column1 + Column2 + ColumnEtc AS OnePieceOfData FROM TheOtherTable
 

[Iprogramforlawyers]

you'll have to do it by query with the fields joined as one with "+" signs

INSERT INTO YourTable (yourColumn) SELECT Column1 + Column2 + ColumnEtc AS OnePieceOfData FROM TheOtherTable
 

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:

CREATE PROCEDURE prcCombineInquiryNotes (@inquiry_id int, 
@massive_note varchar(8000) OUTPUT
)
AS
 
--these hold what's in the select statement each go around
DECLARE @username varchar(50)
DECLARE @notes varchar(500)
DECLARE @noteDate datetime
 
 
DECLARE curInqNotes CURSOR FOR
 
SELECT    inquiry_notes.notes, CONVERT(VARCHAR, inquiry_notes.note_date_time, 101) AS noteDate, administrator.username
FROM      inquiry_notes INNER JOIN
          administrator ON inquiry_notes.admin_id = administrator.member_id
WHERE     (inquiry_notes.inquiry_id = @inquiry_id)
 
-- open cursor
OPEN curInqNotes
 
    -- Perform the first fetch.
    FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
 
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Combines multiple notes into one note
 
        select @massive_note = 'Note: ' + @notes + CHAR(10)
        select @massive_note = @massive_note + 'Left by: ' + @username + ' On: ' + CONVERT(VARCHAR, @noteDate, 101) + CHAR(10)
 
           -- This is executed as long as the previous fetch succeeds.
        FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
        END
 
CLOSE curInqNotes
DEALLOCATE curInqNotes
 
RETURN
GO
 

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.

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, 
                      IC.email_address, IC.home_phone, RTRIM(CONVERT(CHAR, I.inquiry_date, 101)) AS OpenDate, LKIT.inquiry_type AS ReferredBy, 
                      'Inquiry ID#: ' + CONVERT(VARCHAR, I.inquiry_id) + ' Subject: ' + I.inquiry_subject + CONVERT(VARCHAR(3000), I.inquiry) AS Notes, 'NC' AS Class, 
                      'LK' AS Attorney
 
FROM         dbo.inquiry I INNER JOIN
                      dbo.inquiry_contact IC ON I.inquiry_contact_id = IC.inquiry_contact_id INNER JOIN
                      dbo.lk_inquiry_type LKIT ON I.inquiry_type_id = LKIT.inquiry_type_id
WHERE     (I.division_id = 5) AND (I.inquiry_status_id = 3)
 

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?

[CaptainD]

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?

[vj1001]

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:

CREATE PROCEDURE prcCombineInquiryNotes (@inquiry_id int, 
@massive_note varchar(8000) OUTPUT
)
AS
 
--these hold what's in the select statement each go around
DECLARE @username varchar(50)
DECLARE @notes varchar(500)
DECLARE @noteDate datetime
 
 
DECLARE curInqNotes CURSOR FOR
 
SELECT    inquiry_notes.notes, CONVERT(VARCHAR, inquiry_notes.note_date_time, 101) AS noteDate, administrator.username
FROM      inquiry_notes INNER JOIN
          administrator ON inquiry_notes.admin_id = administrator.member_id
WHERE     (inquiry_notes.inquiry_id = @inquiry_id)
 
-- open cursor
OPEN curInqNotes
 
    -- Perform the first fetch.
    FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
 
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Combines multiple notes into one note
 
        select @massive_note = 'Note: ' + @notes + CHAR(10)
        select @massive_note = @massive_note + 'Left by: ' + @username + ' On: ' + CONVERT(VARCHAR, @noteDate, 101) + CHAR(10)
 
           -- This is executed as long as the previous fetch succeeds.
        FETCH NEXT FROM curInqNotes INTO @notes, @noteDate, @username
        END
 
CLOSE curInqNotes
DEALLOCATE curInqNotes
 
RETURN
GO
 

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.

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, 
                      IC.email_address, IC.home_phone, RTRIM(CONVERT(CHAR, I.inquiry_date, 101)) AS OpenDate, LKIT.inquiry_type AS ReferredBy, 
                      'Inquiry ID#: ' + CONVERT(VARCHAR, I.inquiry_id) + ' Subject: ' + I.inquiry_subject + CONVERT(VARCHAR(3000), I.inquiry) AS Notes, 'NC' AS Class, 
                      'LK' AS Attorney
 
FROM         dbo.inquiry I INNER JOIN
                      dbo.inquiry_contact IC ON I.inquiry_contact_id = IC.inquiry_contact_id INNER JOIN
                      dbo.lk_inquiry_type LKIT ON I.inquiry_type_id = LKIT.inquiry_type_id
WHERE     (I.division_id = 5) AND (I.inquiry_status_id = 3)
 

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/