Querying a really badly designed table
I am working on an application with a number of tables (which I can't change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3, c3... The tables contain 5 to 10 sets of data in each row.
I want to create a stored procedure to return a cursor with a row for each set of data for a row of the table. I know I can use unions:
select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk
union
select a2, b2, c2... from sometable where pk=@pk
union
select a3...
Is there a way that is more efficient (eliminates requerying the table and all the unions)?
[609 byte] By [
GregLee] at [2007-12-19]
Been there done that :) If the data isn's so terribly large, consider building a view:
create view someLessDenormalizedTable
as
select sometable.key, 1 as order, a1 as a, b1 as b , c1 as c... from sometable
union ALL --make sure to use ALL to avoid the sort
select sometable.key, 2 as order, a2, b2, c2... from sometable
union
select a3...
Can't guarantee performance (of course) but it would certainly be worth a try. You could get rid of null rows also.
