F1 F2 F3 C1 C2 C3
------------------------------------------------
A B C 1
D E F 2
A B C 3
D E F 4
A B C 5
D E F 6
i have a table like this.
i need my table to be free from Data redundancy
like this
F1 F2 F3 C1 C2 C3
-----------------------
A B C 1 3 5
D E F 2 4 6
i am unable to figure out the optimized logic.
can someone help me out.?
2
I will assume you are talking about a table in a RDBMS.
That process is called normalization.
The problem is caused because TABLE1 doesn’t have a primary key or at least a unique index to begin with.
If TABLE1 had a PK (or a unique index), say F1,F2,F3, you could not have inserted more than one row with the same values fort those columns. That would have forced you to update the rows instead of inserting a new one.
Your question is not clear, but if what you want is somehow get TABLE2 from TABLE1, this would do it:
create table2 as
select
f1,
f2,
f3,
max(c1) c1,
max(c2) c2,
max(c3) c3
from
table1
group by
f1, f2, f3
as long as there is no overlapping values as the sample seems to suggest.
I don’t know what non-optimized logic you’ve tried, but this works on the dataset example you gave:
select
f1
, f2
, f3
, max(c1) as c1
, max(c2) as c2
, max(c3) as c3
from #RawData
group by f1, f2, f3