Multiple values into one row?

[cjkenworthy]

Is there anyway of getting the values of an attribute (across several rows) into one single row?

If I have:

Company Date Surname Notes
------------------------------------------------
IBM 01/04 Smith Here are some notes
IBM 07/09 Smith This person also made some notes
IBM 18/11 Smith Made some books

I want just one row with all the notes in:

Company Surname Notes
-------------------------------------------
IBM Smith 01/04 Here are some notes 07/09 This
person also made some notes 18/11 Made
some books


Any ideas?

Thanks

 

[donutman]

There are several ways to approach this and the best approach depends upon how often you need this, size of the table and where the data goes from the query.
One method is to write a function that creates the string you are looking for. The function must be passed the CompanyName and SurName.

CREATE FUNCTION dbo.AppendNotes 
   (@CompanyName varchar(30), @Surname varchar(30))
RETURNS varchar(8000)
AS
BEGIN
   Declare @Notes varchar(8000)
   Set @Notes=''
   Select @Notes=@Notes+convert(varchar(5),YourDate,105)
          +' '+ Notes 
      From YourTable 
      Where CompanyName=@CompanyName and SurName=@SurName
   Return @Notes
END

You can then use it as

Select CompanyName, 
       SurName, 
       AppendedNote=dbo.AppendNotes(CompanyName, SurName)
   From YourTable
   Group By CompanyName, SurName, AppendedNote

Because UDFs force a row by row processing of the rowset, you will find this to be slow. There may be another approach for better performance, but I can't think of it right now.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Ouch. I can't stand it! That's going to be slow. Try using the function this way, I think it will be faster.

Select YT.CompanyName, 
       YT.SurName, 
       AppendedNote=dbo.AppendNotes(YT.CompanyName, YT.SurName)
   from (Select Distinct CompanyName, SurName from YourTable) YT

Warning: By using a UDF you have entered a world of evil.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[cjkenworthy]

Thanks, I'll give it a try.