How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

Post by oj » Fri, 03 May 2002 08:57:08



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

 
 
 

How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

Post by Dieter N?t » Fri, 03 May 2002 17:33:35



> 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.

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

 
 
 

How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

Post by Greg Larse » Sat, 04 May 2002 00:48:24


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

 
 
 

How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

Post by Carl Feder » Sat, 04 May 2002 07:23:20


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!

 
 
 

1. name/value pairs in a table, need help with reporting techniques

Hi, I was wondering if someone could lend some experience or
assistance to this problem.

I have a table.

t_stuff
--------
stuff_id int,
stuff_name varchar(10),
stuff_propname varchar(10),
stuff_propvalue varchar(10)

There could be multiple rows for any given "stuff_name" that
correspond to the number of name/value pairs inserted into the table.
I'd like to be able to "turn the row on its side", basically be able
to (through either a view or stored procedure, which ever would be
faster) create a report that would set all of the "stuff_propname"
fields as columns, with the "stuff_propalue"'s displayed in the
corresponding row for that column.

some sample data to make this more clear:

stuff_id stuff_name stuff_propname  stuff_propvalue
-------- ---------- -------------- -----------------
1         apple       color           red
2         apple       taste           yummy
3         apple       origin          new york state
4         tomato      color           red
5         tomato      taste           hearty
6         tomato      origin          new jersey
7         tomato      seeds           true

report would look something like this

stuff_name  color  taste   origin          seeds
----------  -----  -----   ------          -----
apple       red    yummy   new york state  null
tomato      red    heary   new jersey      true

thanks for any and all help,
Ed

2. - Oracle Manufacturing Functional and Technical Consultants

3. Extracting name value pairs from an XML string

4. How to send Date type in an Update query

5. PL/SQL Code: Parsing Name Value Pairs.

6. 'Text'-Type und ASP-ODBC Abfrage

7. Issue with SPs returning name-value pairs

8. Problem with NextRecorset method in ADO ..

9. Modelling items with attribute name/value pairs

10. find all tablename,columnname pairs for a given db

11. creating table with pairs of similar records out of other table

12. Comparing a pair of values in a IN clause