June 26, 2013 at 7:07 pm
create table dbo.tableinfo
(
list1 varchar(10),
list2 varchar(10),
Num int
)
insert into dbo.tableinfo values
('A','A',5),
('A','B',2),
('A','C',7),
('A','D',2),
('B','A',2),
('B','B',4),
('C','A',7),
('C','B',6),
('C','C',7),
('C','D',8),
('C','E',3),
('D','A',2),
('D','B',5),
('D','C',8)
Expected out put is as following:
A B 2
A C 7
A D 2
C B 6
C D 8
C E 3
D B 5
Condition1, is do not display same same in list1 and list2, eg A A 5
condition2, is do not display "A B 2" and "B A 2" twice, show any one only once.
June 27, 2013 at 12:43 am
The first condition was an easy one: list1 <> list2
To get the other condition was a bit harder. In my solution I combined list1 and list2 together, with the highes value first (see the CASE statement). Then I applied a rownumber grouped by this combination. Finally, the requested result is everything with rownumber one.
;with cte_combined as
(
select
ROW_NUMBER() over (PARTITION BY
case when list1 > list2
then list1 + list2
else list2 + list1
end
ORDER BY list1)
as rownr
, list1
, list2
, num
from tableinfo
where list1 <> list2
)
select
list1
, list2
, num
from cte_combined
where rownr = 1
order by list1
edit: removed some [tab] in code for more readability
June 27, 2013 at 6:26 am
Thanks so much, that's awesome
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply