how to convert distinct row values into column

Question

Here is my data

Id  type  count
1   jim     2
1   bim     2
1   sim     3
1   pim     1
2   jim     2
2   bim     1

Want to convert this data into below

Id  jim  bim  sim  pim
1   2     2    3     1
2   2     1    0     0

Tried this, its now working

select * FROM table
    PIVOT
    (
    Min(Id)
    FOR Id IN (select distinct(type) from table)
    )

I'm trying to convert all distinct values of type row into columns and then assign respective values against every ID. Any suggestion please ?

Error

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 26 Column: 16

This is not a task for SQL, but for your app or Website. SQL merely gets you a table of result rows with before-known columns and a variable number of rows. As you don't know the names/types beforehand, you cannot write the query. What you can do is use dynamic SQL. This means you write one query to get the names, then you use a programming language to build the final query from this result and run this. In Oracle you can use the built-in programming language PL/SQL for this. But well, you can just as well simply query the data in rows and loop through them in your app. — Thorsten Kettner, Jan 26 '22 at 06:42

score 0 · Answer 1 · answered Jan 26 '22 at 06:34

0

Would this do?

SQL> with test (id, type, count) as
  2  (select 1, 'jim', 2 from dual union all
  3   select 1, 'bim', 2 from dual union all
  4   select 1, 'sim', 3 from dual union all
  5   select 1, 'pim', 1 from dual union all
  6   select 2, 'jim', 2 from dual union all
  7   select 2, 'bim', 1 from dual
  8  )

  9  select id, nvl(jim, 0) jim,
 10             nvl(bim, 0) bim,
 11             nvl(sim, 0) sim,
 12             nvl(pim, 0) pim
 13  from test
 14  pivot
 15    (min(count)
 16     for type in ('jim' as jim, 'bim' as bim, 'sim' as sim, 'pim' as pim)
 17    );

        ID        JIM        BIM        SIM        PIM
---------- ---------- ---------- ---------- ----------
         1          2          2          3          1
         2          2          1          0          0

SQL>

answered Jan 26 '22 at 06:34

Littlefoot

131,892
15
35
57

but if we are not aware of total distinct(type) ? – Julie Jan 26 '22 at 06:35
If you'd want to *dynamically* use type names, Oracle - as far as I can tell - doesn't support it. You could try with XML or user-defined functions. Have a look at https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql – Littlefoot Jan 26 '22 at 07:10
@Julie I've added [a new answer](https://stackoverflow.com/a/70864129/2778710) to the duplicated question that may solve your issue. But: 1) it will be hard to consume this data inside SQL or PL/SQL, because you cannot quess column names; 2) if you consume this query outside the database, then you may use `sys_refcursor` opened for dynamic query and process it at the application side (external code). – astentx Jan 26 '22 at 13:28

how to convert distinct row values into column

1 Answers1