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!