This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find a distinct combination of the cross columns.
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create table tbl_crossdistinct ( col1 varchar(10) ,col2 varchar(10) ) GO insert into tbl_crossdistinct values ('ABC','BCA'),('BCA','CAD') ,('ABC','CAD'),('BCA','ABC'),('DAC','CAD') ,('EFA','GHI'),('CAD','BCA') GO |
Input data:
|
1 2 3 4 5 6 7 8 9 |
col1 col2 ---------- ---------- ABC BCA BCA CAD ABC CAD BCA ABC DAC CAD EFA GHI CAD BCA |
Expected Output:
|
1 2 3 4 5 6 7 |
col1 col2 ---------- ---------- ABC BCA ABC CAD BCA CAD DAC CAD EFA GHI |
Solution 1:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
select a.* from tbl_crossdistinct as a left join tbl_crossdistinct as b on a.col1 = b.col2 and a.col2 = b.col1 where b.col1 is null union select a.* from tbl_crossdistinct A join tbl_crossdistinct B on a.col1 = b.col2 and a.col2 = b.col1 where a.col1 <= a.col2 order by col1 |
Solution 2:
|
1 2 3 4 5 6 7 8 9 |
SELECT col1,col2 FROM (select *,rank() over(order by col1, col2) rank from tbl_crossdistinct) as t1 EXCEPT SELECT t2.col1,t2.col2 FROM (select *,rank() over(order by col1, col2) rank from tbl_crossdistinct) as t2 INNER JOIN (select *,rank() over(order by col1, col2) rank from tbl_crossdistinct) as c ON (c.col1 = t2.col1 AND c.col2 = t2.col2 ) OR (c.col2 = t2.col1 AND c.col1 = t2.col2 ) WHERE t2.RANK > c.RANK |
Solution 3 (Using ascii value, if all chars same then it will work):
|
1 2 3 4 5 6 7 8 9 10 11 12 |
create table tbl_crossdistinct2 ( col1 varchar(10) ,col2 varchar(10) ) GO insert into tbl_crossdistinct2 values ('AAA','BBB'),('BBB','CCC') ,('BBB','AAA'),('DDD','CCC'),('EEE','FFF') ,('GGG','FFF'),('CCC','DDD') GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
;with ctetest as ( select 1 rnk, ascii(substring(col1,1,1)) + ascii(substring(col2,1,1)) as string,col1, col2 from tbl_crossdistinct2 union all select rnk + 1 rnk, ascii(substring(col1,rnk+1,1)) + ascii(substring(col2,rnk+1,1)) as string, col1, col2 from ctetest where (rnk < datalength(col1) or rnk < DATALENGTH(col2)) ), CTE as ( select string,col1,col2, ROW_NUMBER() over (partition by string order by string) as flag from ctetest ) select col1,col 2 from CTE where flag < 2 |
Please try the different solution for this puzzle and share it via comment...
Leave a Reply