pivot help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

pivot help

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-13 : 07:11:13

hi,
I have a table like :

date time value header
13/12 10:30 30 all
13/12 10:30 50 new
13/12 10:30 70 old

how can I use pivot to display the data like :

all new old
30 50 70

where the date and time are the most recent..

thank you.
jami

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 07:13:29

you only need one row of result ?

KH
[spoiler]Time is always against us[/spoiler]

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-13 : 07:18:12

yes, at the moment, just the most recent.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 07:23:35

then you don't really need to use PIVOT


select top 1 @all = value from table where header = 'all' order by datetime desc
select top 1 @new = value from table where header = 'new' order by datetime desc
select top 1 @old = value from table where header = 'old' order by datetime desc
select @all, @new, @old

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 07:33:12

also try

select top 1 
	case when header = 'all' then value end as all,
	case when header = 'new' then value end as new,
	case when header = 'old' then value end as old,
 from table order by datetime desc

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-13 : 07:33:37

it says : Must declare the scalar variable "@all".

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 07:34:20

quote:
Originally posted by madhivanan

also try
select top 1
case when header = 'all' then value end as all,
case when header = 'new' then value end as new,
case when header = 'old' then value end as old,
from table order by datetime desc

Madhivanan

Failing to plan is Planning to fail

that will return one one record, it will be either header = all or new or old.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-13 : 07:35:03

quote:
Originally posted by jamie

it says : Must declare the scalar variable "@all".

i assume you know how to declare variable ?


declare @all int, @new int, @old int

KH
[spoiler]Time is always against us[/spoiler]

jamie
Aged Yak Warrior

542 Posts

Posted - 2007-12-13 : 07:37:27

oh yeas. thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 07:39:28

quote:
Originally posted by khtan

quote:
Originally posted by madhivanan

also try
select top 1
case when header = 'all' then value end as all,
case when header = 'new' then value end as new,
case when header = 'old' then value end as old,
from table order by datetime desc

Madhivanan

Failing to plan is Planning to fail

that will return one one record, it will be either header = all or new or old.

KH
[spoiler]Time is always against us[/spoiler]

Well. Then it must be something like

select 
	case when header = 'all' then value end as all,
	case when header = 'new' then value end as new,
	case when header = 'old' then value end as old,
from table where datecol=(select max(datecol) from table)

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 10:06:58

quote:

Well. Then it must be something like

select
case when header = 'all' then value end as all,
case when header = 'new' then value end as new,
case when header = 'old' then value end as old,
from table where datecol=(select max(datecol) from table)

no ....

just:

select
sum(case when header = 'all' then value end) as all,
sum(case when header = 'new' then value end) as new,
sum(case when header = 'old' then value end) as old
from table

- Jeff
http://weblogs.sqlteam.com/JeffS

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 01:37:02

Thanks. I missed out the sum part

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources