SQL数据上的动态透视

这应该有效:

我将表创建为

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" ) );

让我们插入一些数据:

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;

有趣的是:

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

计算你最近的变化和每年的变化,留给读者作为练习。

使用以下代码检索一个条目的值更改。通过使用while循环或光标,并使用下面的查询对检索到的所有数据执行联合,您可以为主表中的多个条目重现此过程。

   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