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.

Just to be clear, you only want to return up to three towns correct? If so, you could look at assigning a rank (1, 2 or 3) to each address, then use that rank value to run some insert statements that would populate appropriate columns in your result table. — Pete M
– Pete M, Commented Apr 7, 2011 at 21:48
Added a working example. It's rough and may not be the best way to do things, but hopefully it at least gives you some ideas. — Pete M
– Pete M, Commented Apr 7, 2011 at 22:11

Martin Smith · Accepted Answer · 2011-04-07 22:36:17Z

3

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.

edited Apr 7, 2011 at 22:36

answered Apr 7, 2011 at 22:17

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pete M · Accepted Answer · 2011-04-07 22:10:28Z

Here, check it out:

-- Create People (not like that... jeez...)
CREATE TABLE #People (PersonID INT, Forename VARCHAR(25), Surname VARCHAR(25))
INSERT INTO #People VALUES (1, 'Andy', 'Cairns')
INSERT INTO #People VALUES (2, 'John', 'Smith')

-- Create historical addresses
CREATE TABLE #Addy (PersonID INT, AddyDate DATETIME, Street VARCHAR(50), Town VARCHAR(50))
INSERT INTO #Addy VALUES (1, '2011-01-01', 'Main Street', 'MyTown')
INSERT INTO #Addy VALUES (1, '2010-01-01', 'Old Street', 'OldTown')
INSERT INTO #Addy VALUES (2, '2010-01-01', 'Diagon Alley', 'London')
INSERT INTO #Addy VALUES (1, '2009-01-01', 'First Street', 'OtherTown')

-- Create ranked addresses mapped to people
SELECT p.Forename, p.Surname, a.*, 
        ROW_NUMBER() OVER (PARTITION BY p.PersonID ORDER BY p.PersonID) As Ordinal 
    INTO #Ranked
FROM #People p INNER JOIN #Addy a ON p.PersonID = a.PersonID

-- Make sure everything is kosher
SELECT * FROM #People
SELECT * FROM #Addy
SELECT * FROM #Ranked

-- Create a container for "final" results
DECLARE @Results TABLE (PersonID INT, Forename VARCHAR(25)
            , MoveDate1 DATETIME, Street1 VARCHAR(50), Town1 VARCHAR(50)
            , MoveDate2 DATETIME, Street2 VARCHAR(50), Town2 VARCHAR(50)
            , MoveDate3 DATETIME, Street3 VARCHAR(50), Town3 VARCHAR(50))

-- Get our people primed in the results table
INSERT INTO @Results (PersonID, Forename) SELECT PersonID, Forename FROM #People

-- Fill it up
UPDATE @Results SET MoveDate1 = AddyDate, Street1 = Street, Town1 = Town FROM #Ranked INNER JOIN @Results r ON #RAnked.PersonID = r.PersonID WHERE Ordinal = 1
UPDATE @Results SET MoveDate2 = AddyDate, Street2 = Street, Town2 = Town FROM #Ranked INNER JOIN @Results r ON #RAnked.PersonID = r.PersonID WHERE Ordinal = 2
UPDATE @Results SET MoveDate3 = AddyDate, Street3 = Street, Town3 = Town FROM #Ranked INNER JOIN @Results r ON #RAnked.PersonID = r.PersonID WHERE Ordinal = 3

-- Winsauce?
SELECT * FROM @Results

-- Cleanup
DROP TABLE #People
DROP TABLE #Addy
DROP TABLE #Ranked

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related