All Forums Database
KVB 124 posts Joined 09/12
05 Dec 2013
Distinct Logic

I have a table like
ct table1(name1 varchar(10),name2 varchar(10))
ins into table1 values('ABC','XYZ')
ins into table1 values('ABC','ABC')
ins into table1 values('XYZ','DEF')
ins into table1 values('ABC','DEF')
ins into table1 values('GHI','DEF')
I want my output to be
I need distinct names with the indicators like below
NAME NAME1 NAME2
ABC  Y Y
XYZ Y Y
DEF Y Y
GHI Y N

Raja_KT 1246 posts Joined 07/09
05 Dec 2013

Hi,
Can you please explain the logic how do you arrive at the output so that it will be easier put forth? How do you get ABC Y Y, because I can see ABC in four places.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
05 Dec 2013

Hi Bikky,
Hope the following query will dolve your problem, Or at least give you a start.

CT TD3471.table1(name1 VARCHAR(10),name2 VARCHAR(10));
INS INTO TD3471.table1 VALUES('ABC','XYZ');
INS INTO TD3471.table1 VALUES('ABC','ABC');
INS INTO TD3471.table1 VALUES('XYZ','DEF');
INS INTO TD3471.table1 VALUES('ABC','DEF');
INS INTO TD3471.table1 VALUES('GHI','DEF');

SELECT * FROM TD3471.table1;

SELECT  DISTINCT NAMES,
CASE WHEN A.NAME1 IS NULL THEN 'N' ELSE 'Y' END AS NAME1
,CASE WHEN B.NAME2 IS NULL THEN 'N' ELSE 'Y' END AS NAME2
FROM
(
SELECT NAME1 AS NAMES
FROM TD3471.table1
UNION
SELECT NAME2 AS NAMES
FROM TD3471.table1
)AS DRV
LEFT JOIN TD3471.table1 A
ON DRV.NAMES = A.NAME1
LEFT JOIN TD3471.table1 B
ON DRV.NAMES = B.NAME2;

 

Khurram

dnoeth 4628 posts Joined 11/04
05 Dec 2013

Seems you want an indicator if a Name exists in Name1 or Name2, but your result doesn't match.

SELECT name, MAX(Name1), MAX(Name2)
FROM
 (
   SELECT name1 AS name, 'Y' AS Name1, 'N' AS Name2 FROM table1
   UNION ALL
   SELECT name2, 'N', 'Y' FROM table1
 ) AS dt
GROUP BY 1

 

Dieter

You must sign in to leave a comment.