Can I combine values from multiple rows in another table into multiple columns of one row using SQL?
- by Jordi
I have two tables:
T1:
| M_ID | P_ID1 | P_ID2 | rest of T1 columns |
| 0 | 0 | 1 | ... |
| 1 | 2 | 3 | ... |
T2:
| P_ID | Type | A | B |
| 0 | 1 | a | e |
| 1 | 2 | b | f |
| 2 | 1 | c | g |
| 3 | 2 | d | h |
Now, I want to have a query that selects this:
| M_ID | P_1a | P_1b | P_2a | P_2b | rest of T1 columns |
| 0 | a | e | b | f | ... |
| 1 | c | g | c | h | ... |
So, in words: I want to select all columns from T1, but I want to replace P_ID1 with the columns from T2, where the P_ID is equal to P_ID1, and the type is 1, and basically the same for P_ID2.
I can obviously get the information I need with multiple queries, but I was wondering if there is a way that I can do this with one query. Any ideas?
I'm currently using SQL Server 2008r2, but I'd also be interested in solutions for other database software.
Thanks for the help!