Does this work for you:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[nvpairs]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[nvpairs]
--
-- Create an empty table
--
create table [dbo].[nvpairs]
(
[nv_pk] [int] identity (1, 1) not null ,
[nv_id] [int] not null,
[nv_name] [varchar](30) not null,
[nv_svalue] [varchar](30) null,
[nv_nvalue] [real] null,
[nv_ts] [datetime] not null
) on [primary]
--
-- Populate the table
--
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (1, 'volume', NULL, 150, '4/1/2002
1:0:0')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (1, 'color', 'red', NULL,'4/1/2002
1:0:0')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (1, 'volume', NULL, 100, '4/1/2002
1:0:2')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (1, 'origin', 'north', NULL,'4/1/2002 1:0:3')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (1, 'color', 'orange', NULL,'4/1/2002
1:0:5')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (2, 'color', 'green', NULL,'4/1/2002
1:0:6')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (2, 'origin', 'south', NULL,'4/1/2002
1:0:6')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (2, 'volume', NULL, 90, '4/1/2002
1:0:6')
insert into nvpairs (nv_id, nv_name, nv_svalue, nv_nvalue,
nv_ts) values (2, 'volume', NULL, 180, '4/1/2002
1:0:7')
go
--
-- Dump the table in chronological order.
--
from nvpairs a
where a.nv_pk = (select top 1 nv_pk from nvpairs
where
nv_name=a.nv_name
and
nv_id=a.nv_id
order by nv_ts desc)
and nv_id = 1
order by nv_name desc
begin
case when nv_svalue is not null
then
nv_svalue
else ''
end +
case when nv_nvalue is not null
then
ltrim(cast(nv_nvalue as char(8)) )
else ''
end
from nvpairs a
where a.nv_pk = (select top 1 nv_pk from nvpairs
where
nv_name=a.nv_name
and
nv_id=a.nv_id
order by nv_ts desc)
order by nv_name desc
end
Quote:>-----Original Message-----
>Gang
>The solution to the pivoting problem is well know.
However, this one has me
Quote:>stumped.
>Each name/value pair in this table also has a time stamp
(datetime)
>associated with it.
>When I pivot the table, I need to return the "most recent
value" for any
>name value pair.
>I have enclosed the .SQL script that will create a table,
populate it,
>document what I am looking for, and
>show what I am getting.
>Any help would be appreciated.
>Bob