April 20, 2009 at 12:40 pm
I am trying to query a table that is structured as id, type, old value, new value and I want the results returned to have the id and old value, new value for each type in one row. I would think a pivot tables would be the answer but am having some trouble wrapping my head around this.
following is example of table, data and desired results, also first attempt at pivot. I don't know how to get pivot to work on multiple values. Any help appreciated. thanks.
select HIST_LOG,status,date,time from hist
pivot
(max(hist_old_values)
for hist_field_name in ([status],[date],[time]))
as p
create table Hist
(id varchar(255),
type varchar(255),
old varchar(255),
new varchar(255),
logid varchar(255))
insert into hist
select 'test*1','status','a','b','test'
union all
select 'test*2','date','2009-04-15 00:00:00.000','2009-04-17 00:00:00.000','test'
union all
select 'test*3','time','1900-01-01 12:30:31.000','1900-01-01 12:40:31.000','test'
union all
select 'testing*1','status','a','b','testing'
union all
select 'testing*2','date','2009-04-20 00:00:00.000','2009-04-21 00:00:00.000','testing'
union all
select 'testing*3','time','1900-01-01 18:30:31.000','1900-01-01 19:40:31.000','testing'
and i would like results to look like
'test','a','b','2009-04-15 00:00:00.000','2009-04-17 00:00:00.000', '1900-01-01 12:30:31.000','1900-01-01 12:40:31.000'
'testing','a','b','2009-04-20 00:00:00.000','2009-04-21 00:00:00.000','1900-01-01 18:30:31.000','1900-01-01 19:40:31.000'
April 20, 2009 at 3:15 pm
I see where you're headed. One thing you should be aware of is that SQL does not dynamically create pivot tables for X number of values like Excel. You have to know the values in advance, or use dynamic SQL to create it.
So, the first question is, how do you know what exact values you want to turn into columns?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2009 at 5:31 pm
the table in question can only have three possible values in the type column. These values are status, date, and time. And I an trying to get the old value and the new value for each of these type for each ID.
April 21, 2009 at 9:43 am
I wouldn't worry about PIVOT. A little "crosstab" application will handle this and run very quickly for you. Please let me know if you have any questions.
select logid
,max(case when type = 'status' then old else null end) as status_old
,max(case when type = 'status' then new else null end) as status_new
,max(case when type = 'date' then old else null end) as date_old
,max(case when type = 'date' then new else null end) as date_new
,max(case when type = 'time' then old else null end) as time_old
,max(case when type = 'time' then new else null end) as time_new
from #hist
group by logid
order by logid
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 21, 2009 at 10:10 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply