Pivot table with counts - Oracle SQL

Question

I have this table:

table1

id      e_nm            val         count
2572    Fruit Date      20180115    13
2572    Fruit Date      20180504    21
2573    Salad Date      ABC         50
2574    Test Date       20181115    14
2574    Test Date       19991001    29

This table has all the distinct values present for each e_nm (element names) and their counts. This table has thousands of values available with more than 500 element names.

Is there a way I am able to visualise it like the following using Pivot:

id_2572     id_2572_e_nm    id_2573     id_2573_e_nm     id_2574        id_2574_e_nm
20180115    Fruit Date      ABC         Salad Date       20181115       Test Date
20180504    Fruit Date                                   19991001       Test Date

Please note the table column needs to be generated dynamically by reading id from table1.

Answer 1

Although not exactly as you pictured it, I have come up with a solution. Basically you will need to create a new column in the source table named unique id. This is the unique identifier under which you will be pivoting the table.

After that, you will need to use the following query to get the nearly desired output:

SELECT * FROM
(
  SELECT 
    unique_id, 
    e_nm, 
    val
  FROM table1
)
PIVOT 
(
  listagg(val) within group (order by null) as id, listagg(e_nm) within group (order by null) as id_e_nm
  FOR e_nm IN ('Fruit Date','Salad Date','Test Date')
)

This will give you the following output:

body {
    margin: 0;
    font-family: "Helvetica Neue",Helvetica,Arial,sans-serif;
    font-size: 14px;
    line-height: 20px;
    color: #333;
    background-color: #fff;
}

<table class="results table table-bordered table-striped">
  <tbody>
    <tr>
      <th>UNIQUE_ID</th>
      <th>'Fruit Date'_ID</th>
      <th>'Fruit Date'_ID_E_NM</th>
      <th>'Salad Date'_ID</th>
      <th>'Salad Date'_ID_E_NM</th>
      <th>'Test Date'_ID</th>
      <th>'Test Date'_ID_E_NM</th>
    </tr>
    <tr>
      <td>1</td>
      <td>20180115</td>
      <td>Fruit Date</td>
      <td>ABC</td>
      <td>Salad Date</td>
      <td>20181115</td>
      <td>Test Date</td>
    </tr>
    <tr>
      <td>2</td>
      <td>20180504</td>
      <td>Fruit Date</td>
      <td>(null)</td>
      <td>(null)</td>
      <td>19991001</td>
      <td>Test Date</td>
    </tr>
  </tbody>
</table>

Here is the link to the SQL Fiddle query I created to answer your question.

Hope this points you to the right direction :)