0

Hi, I have a table which stores 2 values as follow

REC_TEST
Id_Customer
Id_Apply

Id_Apply can take 2 values, "YES" or "NO" if he passes the test I've designed or not. I want to know if there is a way to take all "YES" and all "NO" from the same table, for example:

Id_Customer[YES] | Id_Customer[NO]
-------------------------------------------------------
203974 | 3284755
.....
and so on...

I've been trying wiht somethin like doing:

SELECT Id_Customer As YES, Id_Customer As NO
FROM REC_TEST
WHERE YES = 'YES'
AND NO = 'NO';

this is obviusly wrong, can you help me get it?

Thank you guys.

4
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by karant
0

Ok, what I need is this, for example lets say I have a table with 2 rows, one Id, and one type for example

id1 -> type_1
id2 -> type_2

I need a query which gives me, all type_1 id's on one column and all type_2 into anotherone like this

type_1 | type_2
id1 | id2

Thanks for your reply

0

Hi, I have a table which stores 2 values as follow

REC_TEST
Id_Customer
Id_Apply

Id_Apply can take 2 values, "YES" or "NO" if he passes the test I've designed or not. I want to know if there is a way to take all "YES" and all "NO" from the same table, for example:

Id_Customer[YES] | Id_Customer[NO]
-------------------------------------------------------
203974 | 3284755
.....
and so on...

I've been trying wiht somethin like doing:

SELECT Id_Customer As YES, Id_Customer As NO
FROM REC_TEST
WHERE YES = 'YES'
AND NO = 'NO';

this is obviusly wrong, can you help me get it?

Thank you guys.

With Header

WITH
 i_data AS (
SELECT r1.id_apply,
       r1.id_customer||'|'||(SELECT r2.id_customer FROM res_test r2 WHERE r2.id_apply != r1.id_apply) AS res
  FROM rec_test ry
 WHERE r1.id_apply = 'YES'
)
,i_header AS (
SELECT 1 AS i,
       'Id_Customer['||id_apply||']|Id_Customer['DECODE(id_apply,'YES','NO','YES')']'||chr(10)||rpad('-',55,'-') AS res
  FROM i_data
UNION
SELECT 2 AS i,
       res
  FROM i_data 
)
SELECT res
  FROM i_header
 ORDER BY i;

Without Header

SELECT r1.id_customer||'|'||(SELECT r2.id_customer FROM res_test r2 WHERE r2.id_apply != r1.id_apply) AS "Id_Cust[YES]|Id_Cust[NO]"
  FROM rec_test ry
 WHERE r1.id_apply = 'YES';
0

If Test is the name oof your table having following columns
REC_TEST
Id_Customer
Id_Apply
then

ALTER table Test
MODIFY COLUMN Id_Customer VARCHAR2(4)

alter table test
add constraint test_id_ck
check(Id_Customer in ('YES','NO')

this will surely help u

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.