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 3
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

Highlighted
Junior Supporter

## 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
`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`