Very Computer

select
 nv_id,
 max(case nv_name when 'volume' then nv_nvalue  else null end) as Volume,
 max(case nv_name when 'color'  then nv_svalue  else null end) as Color,
 max(case nv_name when 'origin' then nv_svalue  else null end) as Origin
from nvpairs n1
where nv_ts=(select max(nv_ts) from nvpairs n2 where n2.nv_id=n1.nv_id)
group by
 nv_id

you can also check out Rac for such pivotting issues!

--
-oj

Quote:> Gang

> The solution to the pivoting problem is well know. However, this one has
me
> 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

select
 nv_id,
 max(case nv_name when 'volume' then nv_nvalue  else null end) as Volume,
 max(case nv_name when 'color'  then nv_svalue  else null end) as Color,
 max(case nv_name when 'origin' then nv_svalue  else null end) as Origin
from nvpairs n join
(select
   nv_id as tmp_id, nv_name as tmp_name, max(nv_ts) as tmp_ts
 from nvpairs
 group by nv_id, nv_name
) tmp
on nv_id = tmp_id
and
  nv_name = tmp_name
and
  nv_ts = tmp_ts
group by
  nv_id
order by nv_id

Dieter

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

The different styles of solutions were interesting.  I did a performance
comparison, based on logical reads,  between the different solutions and
the winner by a factor of 10 is:

Dieter N?th

Note also that his SQL solution requires the least typing an additional
advantage.

Test data:
3906 rows
21 distinct nv_id
Clustered index on (nv_id , nv_name , nv_ts )

Microsoft SQL Server  2000 - 8.00.534 Standard Edition on Windows NT 5.0
(Build 2195: Service Pack 2)

Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!