I have a table where Fields are stored in one Column and their expected results are stored in another Column. The query is the following:
select EMPNO, ICODE, Type, Role from EMP_TBL
The query yields this result:
| EMPNO | ICODE | Type | Training |
|---|---|---|---|
| 579304 | 84338308 | Intro | Welcome In Vid |
| 579304 | 74287748 | Intro | IT Support 2 |
| 579304 | 74728423 | Compliance | Harassment Prev |
| 283749 | 23873832 | Compliance | Benefit Spclst |
| 579304 | 46384742 | Security | Phishing Email |
| 526934 | 21222126 | Security | Key Card Usage |
| 183729 | 90384678 | Project | Requesting Fund |
| 283749 | 28653846 | Project | Schedule Proces |
| 686429 | 94748634 | Role Based | Dashboard 101 |
| 283749 | 22733749 | Role Based | Presentation |
I then used the following query to pivot the fields:
select EMPNO,
case when GROUP = 'Intro' then Role end as "Intro"
case when GROUP = 'Compliance' then Role end as "Compliance"
case when GROUP = 'Security' then Role end as "Security"
case when GROUP = 'Project' then Role end as "Project"
case when GROUP = 'Role Based' then Role end as "Role Based"
from EMP_TBL
The query yields this result:
| EMPNO | Intro | Compliance | Security | Project | Role Based |
|---|---|---|---|---|---|
| 579304 | Welcome In Vid | (null) | (null) | (null) | (null) |
| 345678 | IT Support 2 | (null) | (null) | (null) | (null) |
| 579304 | (null) | Harassment Prev | (null) | (null) | (null) |
| 283749 | (null) | Benefit Spclst | (null) | (null) | (null) |
| 579304 | (null) | (null) | Phishing Email | (null) | (null) |
| 526934 | (null) | (null) | Key Card Usage | (null) | (null) |
| 183729 | (null) | (null) | (null) | Requesting Fund | (null) |
| 283749 | (null) | (null) | (null) | Schedule Proces | (null) |
| 686429 | (null) | (null) | (null) | (null) | Dashboard 101 |
| 283749 | (null) | (null) | (null) | (null) | Presentation |
I am trying to generate a final result where a user with trainings in different types will have all their results in a single row. This would look like the following:
| EMPNO | Intro | Compliance | Security | Project | Role Based |
|---|---|---|---|---|---|
| 579304 | Welcome In Vid | Harassment Prev | Phishing Email | (null) | (null) |
| 345678 | IT Support 2 | (null) | (null) | (null) | (null) |
| 283749 | (null) | Benefit Spclst | (null) | Schedule Proces | Presentation |
| 526934 | (null) | (null) | Key Card Usage | (null) | (null) |
| 183729 | (null) | (null) | (null) | Requesting Fund | (null) |
| 686429 | (null) | (null) | (null) | (null) | Dashboard 101 |
How can I achieve this without a group by as I cannot use group by without aggregating. This isn’t possible as my fields aren’t numerical values.
New contributor
JakeNotFromStatefarm is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.