concatenate script not working

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 2000 Forums

Transact-SQL (2000)

concatenate script not working

Author

Topic

crackerbox
Starting Member

21 Posts

Posted - 2008-10-08 : 11:38:58

I have 2 tables. 1 table has an Flight_Id, aircraft_ID and a Main_Flight_Date. The other table has the Flight_ID and a note. What I want to do is concatenate all the notes in the second table per airplane per day so that I have all the notes for all the flights on one line for each plane and each day. There can be multiple flights (flight_Id) per day for each airplane. I pulled the following script from the internet and I have tried to adapted it but it's not returning any data. Can anyone help me find the problem? My scripting skills are very limited.

CREATE Procedure sp_Combine_NOtes
AS

DECLARE @tbl TABLE (Aircraft_ID varchar(10), Main_Flight_Date datetime, Note_Combined VARCHAR(8000))
SET NOCOUNT ON
DECLARE @c varchar(10),
@d datetime,
@p VARCHAR(8000),
@cNext varchar(10),
@dnext datetime,
@pNext VARCHAR(8000)
DECLARE c CURSOR FOR
SELECT a.Aircraft_ID, c.Main_Flight_Date, rtrim(a.Note)
FROM Test_Flight_Log_QC.dbo.Flight_Log c,
Test_Flight_Log_QC.dbo.Flight_Log_Add_Notes A
Where a.flight_log_doc_no = c.flight_id

ORDER BY a.Aircraft_ID, c.Main_Flight_Date;
OPEN c ;
FETCH NEXT FROM c INTO @cNext, @dnext, @pNext ;
SET @c = @cNext;
Set @d = @dnext ;
WHILE @@FETCH_STATUS = 0 BEGIN
IF @cNext = @c and @dnext = @d BEGIN
INSERT @tbl SELECT @c, @d,@p ;
SELECT @c = @cNext, @d = @dNext ;
END ELSE
SET @p = COALESCE(@p + ',', SPACE(0)) + @pNext ;
FETCH NEXT FROM c INTO @cNext, @dnext, @pNext
END
INSERT @tbl SELECT @c, @d,@p ;

CLOSE c ;
DEALLOCATE c ;
SELECT * FROM @tbl ;

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 11:43:59

you've couple of other methods here. try it out

http://www.projectdmx.com/tsql/rowconcatenate.aspx

crackerbox
Starting Member

21 Posts

Posted - 2008-10-08 : 12:29:38

I'm using SQL 2000 so some of the scripts aren't an option for me. I've visited this address before. I've managed to get a script working with 2 columns. I had an ID and concatenated all the names in the child table for the second column. I can't get the extra levels into one row. This is an example (from a view I created from the two tables)

Aircraft_ID Main_Flight_Date Note

1 3/19/08 Test 1
1 3/19/08 Test 2
1 5/2/08 Test 3
2 4/1/08 Test 4

What I need is

1 3/19 Test 1, Test 2
1 5/2 Test 3
2 4/1 Test 4

Do you have any suggestions from this example?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 13:04:52

make udf like this;-

CREATE FUNCTION dbo.udf_select_concat 
( 
@Aircraft_ID INT,
@Main_Flight_Date datetime
)
    RETURNS VARCHAR(8000) AS BEGIN
    DECLARE @p VARCHAR(8000) ;
           SET @p = '' ;
        SELECT @p = @p + Note + ','
          FROM YourTable
         WHERE Aircraft_ID =@Aircraft_ID 
         AND Main_Flight_Date =@Main_Flight_Date
    RETURN @p
    END

and use it like this

SELECT Aircraft_ID, Main_Flight_Date,
dbo.udf_select_concat(Aircraft_ID, Main_Flight_Date)
FROM yourtable
GROUP BY Aircraft_ID
ORDER BY Aircraft_ID

crackerbox
Starting Member

21 Posts

Posted - 2008-10-08 : 13:50:18

It worked!!!

I've spent 2 days trying to figure this out. Thank you so much!

Sboyd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 14:01:01

quote:
Originally posted by crackerbox

It worked!!!

I've spent 2 days trying to figure this out. Thank you so much!

Sboyd

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources