converting columns to rows comparing multiple records

  • 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

  • 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