April 16, 2016 at 8:43 am
Hi everyone
I've a problem with converting sql server table column values into rows.
I've tried using unpivot which works fine, but it gives me a partial result. I'm stuck from that point.
Ok, i'll move straight to it:
here is my test script (sql 2008)
create table #test (Id int, record_type char(3), sex char(1), age tinyint)
insert into #test (Id, record_type, sex, age)
select 1,'old','M',10 union all
select 1,'new','M',11 union all
select 2,'old','M',23 union all
select 2,'new','F',23
what i want is to use a simple select against some view
select
property, old, new
from
some_view
where
id = 1
where the desired result set looks like the one in the attachment.
The result is in fact a history of changes comparing two records, displayed in a readable manner (property (column), old_Value, new_value)
I forgot to mention: there are more than 30 properties to compare and display and I don't want to use multiple case staments for performance issues.
i'd really like to use a different approach.
I'd appreciate if you help me with this one.
Regards to you all
Marko
April 16, 2016 at 9:19 am
Hi, me again
After doing some gym i came up with the following solution:
;with aa as
(select
id,
record_type,
old.property,
old.value
FROM
(select
id,
record_type,
convert(varchar(250),sex) sex,
convert(varchar(250),age) age
from
#test) data
UNPIVOT
(value FOR property IN (sex,age)) old)
select
old.id,
old.property,
old.value old_value,
new.value new_value
from
aa old
inner join aa new on new.id = old.id and new.property = old.property and old.record_type <> new.record_type
where
old.id = 1 and
old.record_type= 'old'
in case there where more than 2 records per key i'd probably have to use row_number() to filter the resultset
Hope this works for me; i'll do some testing
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply