问题描述:

I have a table (in SQL Server 2012) that looks like this: this

It contains the following fields:

Parcel ID:

  • each parcel has a unique identifier, but are duplicated in my table when a property has been sold multiple times

SaleID:

  • Each SaleID is unique
  • The SaleID will be larger with the second/third/… sale of each parcel - this may be of use for pivoting?

Price:

  • Price of each sale

SaleDate:

  • I’ve coded the dates by month to make calculations easier (with January 2010 = 1, January 2011=13, …)

Count:

  • The number of times each parcel appears in duplicate

I want to create a new table where the ParcelIDs become unique (like this) and each sale is listed in it's own column. I believe I will need a pivot for this...

  • I believe it will involve something with taking each ParcelID, finding which SaleID or SaleDate is lowest and then assigning that to Sale1.

If it would be easier, I could also happily do all the duplicates of 2 at once (and 3 resales, 4, etc... separately) using some sort of "select every other row" type of format..

Is this possible? I'm guessing it will involve a pivot. Any tips would be greatly appreciated!

网友答案:

It is possible to do that, but that's really not a structure where SQL Server is good at, you'll have to quite a bit of trickery to do that. At least one example is here:

SQL Query that groups by sets that are unknown when the query is run

If possible, I would recommend using different kind of output format.

网友答案:

I would add an identity column to standardize the sale order (1,2) instead of (1001,1310). You can then run a case statement to do the pivot. There might be a better way to do this, but it will work in this small example. You will need to make the judgement of how much data is there and is it worth it to add a column.

Example with code to add saleorder: create table test (parcelid int , saleid int , price int , saledate int , count int)

alter table test add saleorder int

insert into test (parcelid, saleid, price, saledate) values (1, 1001, 184000, 38) insert into test (parcelid, saleid, price, saledate) values (1,1310,78000,11) insert into test (parcelid, saleid, price, saledate) values (2,1102,135000,15) insert into test (parcelid, saleid, price, saledate) values (2,1103,42000,17) insert into test (parcelid, saleid, price, saledate) values (3,1100,110000,42) insert into test (parcelid, saleid, price, saledate) values (3,1306,150000,42) insert into test (parcelid, saleid, price, saledate) values (3,1401,58500,42)

declare @parcelid varchar(100)

declare c1 cursor for select distinct parcelid from test order by parcelid open c1 fetch next from c1 into @parcelid while @@fetch_status = 0 begin

exec ('

declare @temp_table table

( id int identity(1,1) , parcelid int , saleid int)

insert into @temp_table
select parcelid
     , saleid
  from test
 where parcelid = ' + @parcelid + '
 order by saleid

 update a
    set saleorder = id
  from test a
  join @temp_table tt
    on a.parcelid = tt.parcelid
   and a.saleid = tt.saleid

')

fetch next from c1 into @parcelid

end

close c1 deallocate c1

select * from test where parcelid = 1 order by saleid

select parcelid , max(case when saleorder = 1 then price end) 'price_1' , max(case when saleorder = 1 then saledate end) 'saledate_1' , max(case when saleorder = 2 then price end) 'price_2' , max(case when saleorder = 2 then saledate end) 'saledate_2' , max(case when saleorder = 3 then price end) 'price_3' , max(case when saleorder = 3 then saledate end) 'saledate_3' --, max(case when saleorder = x then price end) -- add these for the max number of times a parcel has been sold --, max(case when saleorder = x then saledate end) from test group by parcelid order by parcelid

相关阅读:
Top