Member Avatar for RicardoE

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
  • 114 Views
  • 4 Months Discussion Span
  • Latest Post Latest Post by karant

Dani AI

Generated

Two common interpretations of the request should be clarified before choosing a solution. One is a row-wise pairing: list the first YES next to the first NO, second YES next to second NO, etc. The other is an aggregated view: one column contains all YES ids (comma-separated) and the other contains all NO ids in a single row. asked for the side-by-side output; attempted a correlated-subquery concatenation that effectively pairs rows arbitrarily and will break when counts differ or when the subquery returns multiple matches. ‘s suggestion appears to mix up columns (a CHECK belongs on Id_Apply, not Id_Customer) and could corrupt data types.

For a stable row-wise pairing use ROW_NUMBER() to enumerate each group and then FULL OUTER JOIN on the row number. This guarantees predictable pairing and leaves unmatched values as NULL so you can see when counts differ.

SELECT y.yes_cust, n.no_cust
FROM (
  SELECT id_customer AS yes_cust,
         ROW_NUMBER() OVER (ORDER BY id_customer) rn
  FROM rec_test
  WHERE id_apply = 'YES'
) y
FULL OUTER JOIN (
  SELECT id_customer AS no_cust,
         ROW_NUMBER() OVER (ORDER BY id_customer) rn
  FROM rec_test
  WHERE id_apply = 'NO'
) n USING (rn)
ORDER BY rn;

If a single-row summary is acceptable, aggregate each side into a comma-separated list with LISTAGG (or XMLAGG/CLOB if the result may exceed VARCHAR limits):

SELECT
  LISTAGG(CASE WHEN id_apply='YES' THEN id_customer END, ',') WITHIN GROUP (ORDER BY id_customer) AS yes_list,
  LISTAGG(CASE WHEN id_apply='NO'  THEN id_customer END, ',') WITHIN GROUP (ORDER BY id_customer) AS no_list
FROM rec_test;

Notes: pick the ORDER BY inside ROW_NUMBER/LISTAGG to control pairing/sort; add an index on Id_Apply if the table is large; validate data types (Id_Customer numeric vs text) before altering columns or adding constraints.

Recommended Answers

All 4 Replies

Member Avatar for debasisdas

Please reframe the question, explaining the exact requirements clearly.

Member Avatar for RicardoE

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

Member Avatar for anubina

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';
Member Avatar for karant

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.