Distinct Logic

Database
KVB
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 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
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 dt
GROUP BY 1