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

Question

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.

标签： sqlteradatateradata-sql-assistant

Answer 1

只需使用条件聚合：

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;