Table shift field values to columns

Question

My main problem is that I'm not sure what I'm trying to do, so I don't know how to research it. If anyone could give me some terminology or direction or answer I would greatly appreciate it.

I know what my end goal is, I just don't know what the proper terms are.

I have a query that returns this:

select *
from details
where EventId in (1,2,3)

╔═════════╦═════════╦═════╦═══════════════════════╦══════╗
║ EventId ║  Name   ║ Col ║       StrValue        ║ Col6 ║
╠═════════╬═════════╬═════╬═══════════════════════╬══════╣
║       1 ║ Person  ║   0 ║ Alice.Anderson        ║ NULL ║
║       1 ║ Machine ║   0 ║ SPOT-001              ║ NULL ║
║       1 ║ Address ║   1 ║ 10.0.0.14             ║ NULL ║
║       1 ║ Pool    ║   0 ║ GT2                   ║ NULL ║
║       2 ║ Person  ║   0 ║ Bob.Barker            ║ NULL ║
║       2 ║ Machine ║   0 ║ SPOT-006              ║ NULL ║
║       2 ║ Address ║   1 ║ 10.0.0.19             ║ NULL ║
║       2 ║ Pool    ║   0 ║ GT2                   ║ NULL ║
║       3 ║ Person  ║   0 ║ Christine.Christensen ║ NULL ║
║       3 ║ Machine ║   0 ║ SPOT-003              ║ NULL ║
║       3 ║ Address ║   1 ║ 10.0.0.34             ║ NULL ║
║       3 ║ Pool    ║   0 ║ GO1                   ║ NULL ║
╚═════════╩═════════╩═════╩═══════════════════════╩══════╝

I would like a query that returns this, where column 2 and 3 names are based on the fields from the previous table:

╔═════════╦══════════╦═══════════════════════╗
║ EventId ║ Machine  ║        Person         ║
╠═════════╬══════════╬═══════════════════════╣
║       1 ║ SPOT-001 ║ Alice.Anderson        ║
║       2 ║ SPOT-006 ║ Bob.Barker            ║
║       3 ║ SPOT-003 ║ Christine.Christensen ║
╚═════════╩══════════╩═══════════════════════╝

I've been able to get close with a collection of case when statements, but there's always so many nulls and it doesn't collapse like I need it to.

I have tried something like this, and gotten the following:

select 
  case when Name in ('Person','Machine') then EventId end,
  case Name when 'Machine' then StrValue end,
  case Name when 'Person' then StrValue end
from details
where EventId = 1

╔═════════╦══════════╦════════════════╗
║ EventId ║ Machine  ║     Person     ║
╠═════════╬══════════╬════════════════╣
║ NULL    ║ NULL     ║ NULL           ║
║ 1       ║ Spot-001 ║ NULL           ║
║ 1       ║ NULL     ║ Alice.Anderson ║
║ NULL    ║ NULL     ║ NULL           ║
║ NULL    ║ NULL     ║ NULL           ║
║ NULL    ║ NULL     ║ NULL           ║
║ NULL    ║ NULL     ║ NULL           ║
╚═════════╩══════════╩════════════════╝

But I don't know how to collapse those two rows and ignore all the nulls, or if there's a more efficient way to do this on large tables.

Answer 1

I'm pretty sure this would give you the output you're expecting:

select 
  EventId,
  max(case Name when 'Machine' then StrValue end) as machine,
  max(case Name when 'Person' then StrValue end) as person
from details
where EventId = 1
  and name in ('Person','Machine')
group by EventId