SQL "case when" query

Question

enter image description here

I need this for my school project a query that i cannot create.

the table contains 4 columns:

document id, party_id, full name, party role.

And for a doc_id i have 2 rows, 1 for the grantor 1 for the grantee. I want to make s single row but i did not succeed with case when. Please help.

SELECT document_id, 
       case  when party_role ='grantor' then full_name 
       case  when party_role ='grantee' then full_name  
from sales.all_sales

I have some pointers for you...https://stackoverflow.com/questions/14189216/case-in-select-statement — prabhat mishra, Nov 07 '17 at 11:11
I'm not sure I fully understand. So it's one table only with two records per document_id? Please show sample data and desired result. — Thorsten Kettner, Nov 07 '17 at 11:12
https://www.postgresql.org/docs/10/static/functions-conditional.html#functions-case — , Nov 07 '17 at 11:13
Just follow the proper `case` syntax: http://modern-sql.com/feature/case — Markus Winand, Nov 07 '17 at 11:19
below query returns 2 columns. i would like 3: docid, grantor, grantee SELECT document_id, CASE(party_role) WHEN 'grantor' THEN full_name WHEN 'grantee' THEN full_name ELSE '' END FROM sales.all_sales; — John, Nov 07 '17 at 11:42
So there is always exactly two records per document_id; one for grantor, one for grantee? And we can safely ignore the party_id, i.e. it has no meaning here? — Thorsten Kettner, Nov 07 '17 at 11:44
per document id there might be multiple names as grantors and multiple names as grantee case when i would like the names 1 after another — John, Nov 07 '17 at 11:47
i would like the party role (it is either grantor either grantee) to become the name of the column. — John, Nov 07 '17 at 11:48

score 2 · Answer 1 · answered Nov 07 '17 at 11:08

2

SELECT document_id,
       CASE(party_role)
           WHEN 'grantor'
           THEN full_name
           WHEN 'grantee'
           THEN full_name
           ELSE ''
       END
FROM sales.all_sales

answered Nov 07 '17 at 11:08

Yogesh Sharma

49,870
5
26
52

AK47 · Accepted Answer · 2017-11-07T11:56:06.800

0

SELECT DISTINCT document_id, 
   case  when party_role ='grantor' then full_name end Grantor,
   case  when party_role ='grantee' then full_name  end Grantee

from sales.all_sales

if you want to aggregate the field try :

SELECT DOCUMENT_ID,
LISTAGG(GRANTOR,',') WITHIN GROUP (ORDER BY GRANTOR) GRANTOR,
LISTAGG(GRANTEE,',') WITHIN GROUP (ORDER BY GRANTEE) GRANTEE
FROM (
 SELECT DISTINCT document_id, 
   case  when party_role ='grantor' then full_name end Grantor,
   case  when party_role ='grantee' then full_name  end Grantee

from sales.all_sales
)
GROUP BY DOCUMENT_ID

edited Nov 07 '17 at 11:56

answered Nov 07 '17 at 11:47

AK47

58
5

Hi Nucu,If the name of a Grantor or Grantee is repeated with a different party_id then Thorsten's aggregated field will display same name twice. My query has distinct for a reason. – AK47 Nov 07 '17 at 12:19
Yes AK47. I have tested your theory and you are correct. Thank you. – John Nov 07 '17 at 12:48

score 0 · Answer 3 · answered Nov 07 '17 at 11:52

You want aggregation. You want to have one result row per document_id, so group by it. You want to build a string of grantors and grantees each. Use LISTAGG for this.

select
  document_id, 
  listagg(case when party_role = 'grantor' then full_name end, ',')
          within group (order by full_name) as grantor,
  listagg(case when party_role = 'grantee' then full_name end, ',')
          within group (order by full_name) as grantee
from sales.all_sales
group by document_id;

score 0 · Answer 4 · answered Nov 07 '17 at 12:26

In Oracle 11g you can do it with pivot and listagg as aggregate function:

select * from t
 pivot (listagg(name, ', ') within group (order by name) 
        for role in ('grantor' grantors, 'grantee' grantees))

^Demo:

with t(id, name, role) as (
    select 2, 'James',    'grantor' from dual union all
    select 2, 'Tom',      'grantor' from dual union all
    select 2, 'Victoria', 'grantee' from dual union all
    select 2, 'Anty',     'grantee' from dual )
select * from t
 pivot (listagg(name, ', ') within group (order by name) 
                            for role in ('grantor' grantors, 'grantee' grantees))

^Result:

        ID  GRANTORS         GRANTEES
----------  ---------------  -----------------
         2  James, Tom       Anty, Victoria

SQL "case when" query

4 Answers4