CODEWITHSUNDEEP
sqlsql-server-2008t-sqlpivot
d0h
d0h

Reputation: 39

data in multiple rows into single row (values not standard)

here's my problem:

I have a table with Names and Addresses, e.g.

Name  |  Address       | Updated
----------------------------------
a     |  12 lane       | 1/1/2011
b     |  34 avenue     | 1/1/2011
c     |  56 district   | 1/1/2011
a     |  78 avenue     | 8/8/2011
b     |  90 lane       | 8/8/2011
a     |  83 district   | 9/9/2011
a     |  39 road       | 10/10/2011

As you can see, it's possible for people to have multiple addresses. Let's say the maximum number of addresses one person has is 5.

I'm only interested in getting the newest 3 addresses for each person, such that the table would look like:

Name  |  Address_1      |   Address_2       |  Address_3
--------------------------------------------------------------
a     | 78 avenue       |   83 district     | 39 road
b     | 34 avenue       |   90 lane         | NULL
c     | 56 district     |   NULL            | NULL

Note that a's first entry "12 lane" doesn't appear

I've been reading other examples shown on stackoverflow, but i'm not sure if Pivot tables are suitable for what I need, since the addresses are all different

Thanks in advance for any help rendered!

Upvotes: 2

Views: 3182

Answers (3)

Bert te Velde
Bert te Velde

Reputation: 853

You may consider to use two queries:

  • The first using the DISTINCT key to get the unique "names".

  • The second, in a loop over the unique names, to get for each name the 3 most recent addresses, using the TOP clause (in your case TOP 3) and an "ORDER BY Updated DESC" specification.

  • Of course, the queries may be combined using a suitable JOIN.

Upvotes: 0

Johan
Johan

Reputation: 1192

Here is a simple query using ROW_NUMBER()

Since you only want three address columns this might be suitable. The solution is not suitable for a non fix amount of columns.

;with testdata(Name, Address, Updated)
as
(
select 'a','12 lane',convert(datetime, '1/1/2011')
union all
select 'b','34 avenue',convert(datetime, '1/1/2011')
union all
select 'c','56 district',convert(datetime, '1/1/2011')
union all
select 'a','78 avenue',convert(datetime, '8/8/2011')
union all
select 'b','90 lane',convert(datetime, '8/8/2011')
union all
select 'a','83 district',convert(datetime, '9/9/2011')
union all
select 'a','39 road',convert(datetime, '10/10/2011')
)
,tmp
as
(
select  * 
        ,row_number() over(PARTITION by Name order by Updated desc) as rn
from testdata
)
select  x.Name
        ,x.Address
        ,y.Address
        ,z.Address
from    tmp x
left join   tmp y
    on  x.Name = y.Name
    and y.rn = 2
left join   tmp z
    on  x.Name = z.Name
    and z.rn = 3
where x.rn = 1

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

You can pivot it. Key point is assigning row_numbers to addresses in descending order of updates. This produces column names 1, 2, and 3 (and 4, 5 - but these will be ignored by pivot command).

Here is Sql Fiddle with example.

select name, 
       [1] Address_1,
       [2] Address_2,
       [3] Address_3
from
(
  select name,
         address,
         row_number() over (partition by name
                            order by updated desc) rn
    from table1
) o
pivot (min(address) for rn in ([1], [2], [3])) p

Upvotes: 6

Related Questions