Dynamic Pivot on sql data

I have two tables as shown below

The master Table

ID               keyword_tags
----------- ---------------------------------------------
10932        international foo data

and a child table(join clause on id = fk_id)

fk_id       date_value               observ_value
----------- -----------------------  ----------------------
10932       2009-01-01 00:00:00.000  331.888888888
10932       2008-06-01 00:00:00.000  301.888888888
10932       2008-01-01 00:00:00.000  321.777777777
10932       2007-01-01 00:00:00.000  288.449066162
10932       2006-01-01 00:00:00.000  259.789733887

Output required is

ID      keyword_tags             Latest_Value    Latest_Change  Annual_Change
------  ----------------------   -------------   -------------  --------------- 
10932   international foo data   331.888888888   30.000000000   10.111111111

开发者_运维问答where

Latest_Change = observ_value(of most recent date_value) - observ_value(of next most recent date_value)
Annual_Change = observ_value(of most recent date_value) - observ_value(of recent date_value - 1 year)

How do i achieve this using sql-server?

This should work:

I create the tables as

create table master_table(
  id            int not null identity( 1, 1 )
, keyword_tags  nvarchar( 127 ) not null

, constraint "master_PK" primary key clustered( "id" ) );

create table child_table(
  id            int not null identity( 1, 1 )
, fk_id         int not null
, date_value    datetime not null
, observ_value  float not null

, constraint "child_PK" primary key clustered( "id" )
, constraint "child_FK_fkid" foreign key ( "fk_id" )
                                 references master_table( "id" ) );

Let's insert some data:

insert into master_table select N'international foo data';
insert into master_table select N'national baz data';

insert into child_table--( "fk_id", "date_value", "observ_value" )
select 1, N'2009-01-01T00:00:00.000', 331.888888888
insert into child_table--( "fk_id", "date_value", "observ_value" )
select 1, N'2008-06-01T00:00:00.000', 301.888888888
insert into child_table--( "fk_id", "date_value", "observ_value" )
select 1, N'2008-01-01T00:00:00.000', 321.777777777
insert into child_table--( "fk_id", "date_value", "observ_value" )
select 1, N'2007-01-01T00:00:00.000', 288.449066162
insert into child_table--( "fk_id", "date_value", "observ_value" )
select 1, N'2006-01-01T00:00:00.000', 259.789733887;


insert into child_table--( "fk_id", "date_value", "observ_value" )
select 2, N'2003-07-01T00:00:00.000', 142.0
insert into child_table--( "fk_id", "date_value", "observ_value" )
select 2, N'2002-07-02T00:00:00.000', 123.0
insert into child_table--( "fk_id", "date_value", "observ_value" )
select 2, N'2002-07-01T00:00:00.000', 117.0
insert into child_table--( "fk_id", "date_value", "observ_value" )
select 2, N'2001-01-01T00:00:00.000', 107.0;

Now for the interesting part:

with currRow as (
    select fk_id, MAX( date_value ) as currDate
    from child_table
    group by fk_id )
select currRow.fk_id, ct.ID, currRow.currDate, ct.observ_value
into #currRow
from currRow
inner join child_table as ct
    on ct.date_value = currRow.currDate;

with lastEntry as (
    select olderRows.fk_id, MAX( olderRows.date_value ) as date_value
    from #currRow as currRow
    inner join child_table as olderRows
        on  olderRows.fk_id = currRow.fk_id
        and olderRows.date_value < currRow.currDate
    group by olderRows.fk_id ),
oneYearAgo as (
    select olderRows.fk_id, MAX( olderRows.date_value ) as date_value
    from #currRow as currRow
    inner join child_table as olderRows
        on  olderRows.fk_id = currRow.fk_id
        and olderRows.date_value <= DATEADD( YEAR, -1, currRow.currDate )
    group by olderRows.fk_id )
select
      master_table.*
    , currRow.ID as currID
    , currRow.currDate
, currRow.observ_value as currObservValue
    , lastData.id as lastPriorID
    , lastData.date_value as lastPriorDateValue
    , lastData.observ_value as lastPriorObservValue
    , oneYearAgoData.id as oneYearAgoID
    , oneYearAgoData.date_value as oneYearAgoDateValue
    , oneYearAgoData.observ_value as oneYearAgoObservValue
from #currRow as currRow
inner join master_table
    on master_table.id = currRow.fk_id
inner join lastEntry
    on lastEntry.fk_id = currRow.fk_id
inner join child_table as lastData
    on  lastData.fk_id = lastEntry.fk_id
    and lastData.date_value = lastEntry.date_value
inner join oneYearAgo
    on  oneYearAgo.fk_id = currRow.fk_id
inner join child_table as oneYearAgoData
    on  oneYearAgoData.fk_id = oneYearAgo.fk_id
    and oneYearAgoData.date_value = oneYearAgo.date_value

Calculating your Latest_Change and Annual_Change from these is left as an exercise for the reader.

Use the following code to retrieve change in values for one entry. You can reproduce this for multiple entries in the master table by using a while loop or a cursor and performing a union of all data retrieved by using the below query.

   select fk_id,[1] as current_value,[1] - [2] latest_change,[1]-[3] annual_change 
   from 
            ( 
                select top 2 fk_id,observ_value ,ROW_NUMBER() over (order by date_value desc) row from child_table od 
                    where fk_id in(10932)
                union
                select fk_id,observ_value,3 row from child_table 
                    where fk_id=10932 and date_value =(select DATEADD(YY, -1, MAX(date_value))from child_table where fk_id=10932)
            )as source_data pivot 
    (
    max(observ_value) for row in ([1],[2],[3])
    ) pd