March 14, 2011 at 2:09 am
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
Have you looked at UNPIVOT as an option?
-Ki
March 14, 2011 at 5:40 am
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
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