July 20, 2007 at 4:17 am
Hello every one,
I have a table which contains rows with some duplicated column values. I want to display 'NULL' for the duplicated column value based on other column value. For example consider a table 'Sample' as
ID ITEM VALUE1 VALUE2 VALUE3
1 IT1 A B C
1 IT1 A B D
1 IT2 H B C
2 IT1 D E F
2 IT1 A E F
3 IT2 B I J
3 IT2 C I K
Here based on ID and ITEM combination I want to display a 'NULL' for duplicated column values. So I require the result as
ID ITEM VALUE1 VALUE2 VALUE3
1 IT1 A B C
1 IT1 NULL NULL D
1 IT2 H B C
2 IT1 D E F
2 IT1 A NULL NULL
3 IT2 B I J
3 IT2 C NULL K
I am very much thankfull to every one who helps me to solve this problem, and one more thing "I am not at all interested in cursors or complex looping".
Thanks in advance...
Pradeep
July 20, 2007 at 6:17 am
I'm going to cheat by adding a sequence number. Then it becomes fairly straight forward. Without the sequence number I don't see a way.
create table t(
seqno int,
id int,
item varchar(3),
value1 varchar(1),
value2 varchar(2),
value3 varchar(3))
insert into t values(1,1,'IT1','A','B','C')
insert into t values(2,1,'IT1','A','B','D')
insert into t values(3,1,'IT2','H','B','C')
insert into t values(4,2,'IT1','D','E','F')
insert into t values(5,2,'IT1','A','E','F')
insert into t values(6,3,'IT2','B','I','J')
insert into t values(7,3,'IT2','C','I','K')
select t1.id,t1.item,
case when t1.value1=t2.value1 and t1.id=t2.id and t1.item=t2.item then null else t1.value1 end value1,
case when t1.value2=t2.value2 and t1.id=t2.id and t1.item=t2.item then null else t1.value2 end value2,
case when t1.value3=t2.value3 and t1.id=t2.id and t1.item=t2.item then null else t1.value3 end value3
from t t1
left join t t2 on t1.seqno-1=t2.seqno
order by t1.id,t1.item
July 20, 2007 at 6:38 am
This really isn't a data- or SQL-problem at all. This is pure display 'nonsense'.
As such, it belongs on the recieving end to format it, not on the querying side.
/Kenneth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply