Use a table-value function to return data in columns instead of rows

Question

I'm trying to write a query which will take a limited number of historical records and display the results in one row.

For example, I have a table of people:

|PersonID|Forename|Surname
|--------|--------|----------
|00000001|Andy    |Cairns
|00000002|John    |Smith

And a table of all their historical addresses:

|PersonID|Date      |Street       |Town
-------------------------------------------
|00000001|2011-01-01|Main Street  |MyTown
|00000001|2010-01-01|Old Street   |OldTown
|00000002|2010-01-01|Diagon Alley |London
|00000001|2009-01-01|First Street |OtherTown

etc..

I'd like to return the following:

|PersonID|Name  |MoveDate1 |Town1 |MoveDate2 |Town2  |MoveDate3 |Town3
------------------------------------------------------------------------
|00000001|Andy  |2011-01-01|MyTown|2010-01-01|OldTown|2009-01-01|OtherTown
|00000002|John  |2010-01-01|London|          |       |          |

At the moment, I'm using the following query:

select PersonID, Name, s.mdate, s.town
from dbo.people
cross apply dbo.getAddressList as s

And the following table-value function:

alter function [dbo].[getAddressList]
(
 @personID
)
returns
 @addresslisttable
(
  mdate smalldatetime
  town char
)
as
begin
  insert into @addresslist (
    mdate
    town
  )
  select top 3 mdate, town
  from dbo.addresses
      where PersonID = @personID
      order by mdate desc
return
end

Unfortunately, this is returning a new row for each address, like this:

|PersonID|Name|MDate     |Town
|00000001|Andy|2011-01-01|MyTown
|00000001|Andy|2010-01-01|OldTown
|00000001|Andy|2009-01-01|OtherTown

How can I return each returned row in a field instead?

Thanks in advance.

Answer 1

Where possible you should always use inline TVFs in preference to multistatement ones.

ALTER FUNCTION [dbo].[getAddressList] 
(   
@personID INT
)
RETURNS TABLE 
AS
RETURN 
(
WITH cte AS
      (SELECT TOP 3 mdate, town, ROW_NUMBER() OVER (ORDER BY mdate DESC) rn
      FROM dbo.addresses
          WHERE PersonID = @personID
          ORDER BY mdate DESC
        )

    SELECT
    MAX(CASE WHEN rn=1 THEN mdate END) AS MoveDate1,
    MAX(CASE WHEN rn=1 THEN town END) AS Town1,
    MAX(CASE WHEN rn=2 THEN mdate END) AS MoveDate2,
    MAX(CASE WHEN rn=2 THEN town END) AS Town2,
    MAX(CASE WHEN rn=3 THEN mdate END) AS MoveDate3,
    MAX(CASE WHEN rn=3 THEN town END) AS Town3
    FROM cte
)

I'd also investigate the relative performance of not using the TVF at all. And doing a JOIN, ROW_NUMBER() OVER (PARTITION BY PersonID) and the PIVOT technique above.