Hi,We have historical table that is denormalized and sample is shown in following code as table "test" where for each row we use to store 4*3 values(a_*,b_*,c_*,d_*) and that restrict us to only store 4(a,b,c,d) values for each column "col1". Now we are redesigning and creating 2 new tables shown as new_test1(store all not repeated columns) and new_test2 (store values in different rows and no longer have restriction to store only 4 different type of values. But some part of our code can't be changed so is there a way we can create view on new tables that can show the output similar to old table for this application (we can't use the PIVOT function since the values in all a_*,b_*,c_*,d_* are dynamic)Thanks--Rubscreate table test(col1 int primary key, col2 int, col3 int,a_1 int, a_2 int, a_3 int,b_1 int, b_2 int, b_3 int,c_1 int, c_2 int, c_3 int,d_1 int, d_2 int, d_3 int,)goinsert into test values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)insert into test values (2,1,1,2,1,1,2,1,1,3,1,1,4,1,1)insert into test values (3,2,1,2,1,1,3,1,1,3,1,1,3,1,1)go--select * from testcreate table new_test1 (col1 int primary key, col2 int, col3 int)--drop table new_test2create table new_test2 (col1 int , mnemonic int,common_1 int, common_2 int, common_3 int)goinsert into new_test1 values(1,1,1)insert into new_test1 values(2,1,1)insert into new_test1 values(3,2,1)--select * from new_test1insert into new_test2 values(1,1,1,1,1)insert into new_test2 values(1,2,1,1,1)insert into new_test2 values(1,3,1,1,1)insert into new_test2 values(1,4,1,1,1)insert into new_test2 values(2,1,2,1,1)insert into new_test2 values(2,2,2,1,1)insert into new_test2 values(2,3,3,1,1)insert into new_test2 values(2,4,4,1,1)insert into new_test2 values(3,1,2,1,1)insert into new_test2 values(3,2,3,1,1)insert into new_test2 values(3,3,3,1,1)insert into new_test2 values(3,4,3,1,1)--select * from new_test2goselect * from testselect * from new_test2
How to show flat view from normalized tables
Posted - 2011-05-06 : 14:40:46