Rows to Columns in SQL 2000

[KARR]

All,
I have been working with SQL 2K for a while, but this is the first time I am trying (without luck) to take the results and pivot them, as so to speak.

Basically want to take the value in the first row of the 'note' field and combine it with the second row of the note field.

Data Set:

FileNo Date Code Note
Test1 9/28/2009 Strt 100 Main St.
Test1 9/28/2009 CSZ Anytown, NJ 07111

Results: (Looking for)

FileNo Date Note
Test1 9/28/2009 100 Main St. Anytown, NJ 07111

Working in a SQL 2K world, and it doesnt look like I will be moving soon.

Thanks for your help.
Rob

 

[bborissov]

Make a function and call it:

SELECT FileNo, Date, dbo.CombineIt(FileNo, Date) AS Note
 FROM YourTable
GROUP BY FileNo, Date



---- Function Code:
CREATE FUNCTION dbo.CombineIt(@FileNo (type here), @Date datetime (maybe?))
RETURNS varchar(8000)
AS
  BEGIN
     DECLARE @RetVal varchar(8000)
     SET @RetVal = ''
     SELECT @RetVal = @RetVal + Note
         FROM YourTable
     WHERE FileNo = @FileNo AND 
           Date   = @Date
     RETURN @RetVal
  END

NOT TESTED!!!



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.