-2

Please help to achieve below resulting table. I tried joining 3 tables, somehow not getting required result.

Table A 

ID  Type    Value1  Value2
1   X       100     200
1   Y       200     300
2   X       100     200

Table B 

ID  Name
1   P
2   Q 
3   R
4   S

Unique ids from table A with matching values

Desired Result 

ID  Name    x_Value1    x_value2    y_value1    y_value2
1   P       100         200         200         300
2   Q       100         200         0           0
Rashmi Kini
  • 19
  • 6
  • `I tried joining 3 tables` ... what did you try? – Tim Biegeleisen Aug 24 '17 at 11:45
  • 1
    Which DBMS are you using? – Radim Bača Aug 24 '17 at 11:45
  • 1
    Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? Postgres? Oracle? –  Aug 24 '17 at 11:45
  • Ok, for SQL Server you might find this helpful: https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – Radim Bača Aug 24 '17 at 11:54
  • I am using SQL.. I tried below query (joining same table). This query gave me required result except for column 'Name' from table B. I was unable to get desired result joining 3 tables..select a1.id,a1.value1 as value_x, a1.value2 as value_y ,b.value1 as value_x, b.value as value_y from (select * from a where type='x') as a1 left join (select * from a where type='y') on a1.id=b.id – Rashmi Kini Aug 24 '17 at 11:56

1 Answers1

2

If you want to do this using join:

select b.id, b.name, ax.value1 as value1_x, ax.value2 as value2_x,
       ay.value1 as value1_y, ay.value2 as value2_y
from b left join
     a ax
     on b.id = ax.id and ax.type = 'X' left join
     a ay
     on b.id = ay.id and ay.type = 'Y'
where ax.id is not null or ay.id is not null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for this answer and response. I think this will give me all the rows from Table B. I am looking at getting only unique values from table A even in case corresponding entry is not available in Table B, it should appear in the result.. – Rashmi Kini Aug 24 '17 at 12:01
  • @RashmiKini . . . No, this will not give you all rows from `b`. That is why there is a `where` clause. – Gordon Linoff Aug 24 '17 at 12:03
  • Thanks.. I tried this query.. Ideally i should be getting each id only once in the resulting table.. but there are multiple in the result – Rashmi Kini Aug 24 '17 at 12:18
  • @RashmiKini . . . The sample data in *this* question has at most one "X" or "Y" value per `id`. Your actual data would seem to have multiple rows. I would suggest that you ask *another* question with more appropriate sample data and a description of how you want to choose the value that goes in row. – Gordon Linoff Aug 24 '17 at 12:23
  • Thanks for your help.. I realized my 2nd table had duplicate ids which resulted in additional rows.. I added 'select distinct' query in join to the same query which gave me desired result. Thank you. – Rashmi Kini Aug 30 '17 at 09:04