Post reply

Capturing Non-Null Column Names and Values Per Group

  • March 14, 2011 at 2:09 am

    #248185

    Hi,

    Wonder if anyone can help. We are migrating an old application to a new one.

    The old application stores audit data in the following format

    PK_ID, FK_ID, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10

    There are actually many more columns. However, the key point is that only one column has data (other than the ID columns) per row. So data is stored like this:

    PK_ID, FK_ID, Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10

    1,1,Null,Null,Null,Null,Hello World,Null,Null,Null,Null,Null

    2,1,Null,Greetings,Null,Null,Null,Null,Null,Null,Null,Null

    3,1,Null,Null,Null,Null,Null,Null,Null,Null,Null,Welcome

    4,2,Null,Null,Null,Null,Null,Null,Hi,Null,Null,Null

    5,2,Top of the morning,Null,Null,Null,Null,Null,Null,Null,Null,Null

    What we need to do it transpose that format into the new format, which is like this:

    PK_ID, FK_ID, Column Name, Value

    So in the above example we’d end up with this:

    PK_ID, FK_ID, Column Name, Value

    1,1,Column5, Hello World

    2,1,Column2, Greetings

    3,1,Column10, Welcome

    4,2,Column7, Hi

    5,2,Column1, Top of the morning

    What I’m struggling with is how to make reference to the name of the column that isn’t null in each row, whilst maintaining the FK_ID value (the PK_ID value isn’t actually important in this instance).

    The source table is fairly large (250k rows) and there are about 50 columns in total, however, this is a process that will only be repeated a few times over a fortnight period, so efficiency isn’t our main concern.

    Any help would be much appreciated.

    Many thanks,

    Steve

  • March 14, 2011 at 5:30 am

    #1298263

    Have you looked at UNPIVOT as an option?

    -Ki

  • March 14, 2011 at 5:40 am

    #1298266

    I found a few minutes I wasn't expecting to have and mocked up a sample for you:

    create table auditData

    (pk_id int

    ,fk_id int

    ,column1 varchar(15)

    ,column2 varchar(15)

    ,column3 varchar(15)

    )

    go

    insert into auditData (pk_id, fk_id, column1, column2, column3) values (1, 1, Null, Null, 'Hello World')

    go

    insert into auditData (pk_id, fk_id, column1, column2, column3) values (2, 1, Null, 'Greetings', Null)

    go

    insert into auditData (pk_id, fk_id, column1, column2, column3) values (3, 2, 'Welcome', Null, Null)

    go

    select pk_id, fk_id, columnName, columnValue

    from

    (select pk_id, fk_id, column1, column2, column3

    from auditData) colBased

    unpivot

    (columnValue for columnName in (column1, column2, column3))

    as rowBased

    go

    This looks like it's exactly what you're looking for.

    -Ki

  • March 14, 2011 at 7:27 am

    #1298308

    Fantastic!

    Thank you so much. I was aware of PIVOT, but not UNPIVOT!

    Best,

    Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply