Database
Enthusiast

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

3 REPLIES
Enthusiast

Re: Distinct Logic

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

Enthusiast

Re: Distinct Logic

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 NAME2FROM(SELECT NAME1 AS NAMESFROM TD3471.table1UNIONSELECT NAME2 AS NAMESFROM TD3471.table1)AS DRVLEFT JOIN TD3471.table1 AON DRV.NAMES = A.NAME1LEFT JOIN TD3471.table1 BON DRV.NAMES = B.NAME2;`
Khurram
Senior Apprentice

Re: Distinct Logic

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 dtGROUP BY 1`