-1

I have a SQL query with multiple left joins but one of the joins is a little complicated. Using the examples below, I need to output a single row for each ID in the first table. The second table has multiple rows for each ID and consists of parameter name and value pairs which I am trying to output as shown at the very bottom. I only care about 3 specific parameter names.

I have multiple issues at the moment. One is my join lets me add "value" however it creates multiple rows for bob jones when I only want one row. The second is I want just those 3 specific param names and to ignore the rest. If an ID doesn't have a value just return NULL or leave blank.

ID   first    last
1    bob      jones
2    joe      dirt

num  param    value
1    color    green
1    shape    circle
1    food     apple
2    color    red
2    drink    water
2    animal   dog

Desired result

ID   first    last    color    shape    drink
1    bob      jones   green    circle   NULL
2    joe      dirt    red      NULL     water
theo nosis
  • 1
  • 1

1 Answers1

-1

Group the data by user and use conditional aggregation to get the values of each user

select t1.id, t1.first, t1.last,
       max(case when t2.param = 'color' then t2.value end) as color,
       max(case when t2.param = 'shape' then t2.value end) as shape,
       max(case when t2.param = 'drink' then t2.value end) as drink
from table1 t1
left join table2 t2 on t1.id = t2.id
group by t1.id, t1.first, t1.last
juergen d
  • 201,996
  • 37
  • 293
  • 362