Search code examples
sqlteradatateradata-sql-assistant

Turn Multiple Rows into 1 Column with Multiple Fields in Teradata SQL

I have a table that looks like this in Teradata SQL Assistant:

Software Name   Employee
Word            Bob
Excel           Bob
Word            Kim
Excel           Kim
PowerPoint      Bob

I want to create it so that for each software, there is only 1 row and each employee is stored as a binary 1 or 0 based on whether they have the software or not. The Final Table should look like this:

Software Name    Bob   Kim
Word             1      1
Excel            1      1
PowerPoint       1      0

I'm using Teradata 15.10 and I haven't been able to figure it out how to do this because Teradata 15.10 does not support the PIVOT function.

Solution

  • Just use conditional aggregation:

    select software_name,
       sum(case when employee = 'Bob' then 1 else 0 end) as Bob,
       sum(case when employee = 'Kim' then 1 else 0 end) as Kim
from t
group by software_name;