Group By a selected variable in Oracle SQL

Question

Sorry for the ambiguous title but I'm really not sure what it's called that I'm looking for.

I have a couple of tables which aren't necessarily great for reporting, that you guessed it, I want to report from. I'm only new to SQL too which complicates things a bit.

Table1:

ID          Created      EventName
1           1/1/14       Event1
2           1/1/14       Event1
3           2/1/14       Event1
4           2/1/14       Event2
5           3/1/14       Event3

Table2:

EventID     EventType    Value
1           Var1         123
1           Var2         XYZ
1           Var3         ABC321
2           Var1         987
2           Var4         Dumbledore
2           Var2         WXY
2           Var6         Hamburger
3           Var1         456

I've figured out that if I use the following SQL...

SELECT T1.EventName,
  Count
  ((Select T2.Value
  From Table2.T2
  Where T1.ID = T2.EventID
  And T2.EventType = 'Var2'
  )) as Var2Count

From Table1 T1

Group By T1.EventName

I should get an output like this:

EventName    Var2Count
Event1       1
Event2       1

However, I'm keen on getting the following output:

EventName    Var2Name
Event1       XYZ
Event2       WXY

I know that it's something about how I structure the Group By, and moreso defining the Var2Name variable after the last From statement, but I'm a little lost exactly how to do that.

..........

Okay, I've figured that this is closer to what I want but there are still issues:

Thanks, I definitely needed to do a join... but I think the next step of my function becomes an issue!

I've got this now:

select Table1.EventName, Table2.Value,

  count
  ((select Table2.Value
  from Table2
  where Table1.ID = Table2.EventID
  and Table2.EventType in ('Var2')
  )) as LineCount,

  sum
  ((select Table2.Value
  from Table2
  where Table1.ID = Table2.EventID
  and Table2.EventType = 'Var7'
  and Table2.Value not like '%undefined%'
  )) as TotalCost

from Table1
inner join Table2
on Table1.ID = Table2.EventID

where Table2.EventType in ('Var2','Var7')

Group By Table1.EventName, Table2.Value;

I just don't know how I would add each of the grouped Table.Value's to a new column...

Output would be like the following:

EventName    Var1Name    Var2Name     Var3Name     Var4Name    (etc)
Event1       123         XYZ          ABC321       (null)
Event2       678         LKT          (null)       765AWW
Event3       (null)      (null)       DERP         STUFF

Any ideas?

Answer 1

I think you want a join, not an aggregation:

select t1.EventName, t2.Value
from table2 t2 join
     table1 t1
     on t2.EventId = t1.id
where t2.EventType = 'Var2';