Pivot On Joined Tables Sql Server

I have two tables with data TAB1 --------------------------------------------- | ID1 | ID2 | SIGNEDBY | A | B | C | | 1 | 8 | 'aa' |'John' | 9 | 12/12

Solution 1:

Here's an alternative option to PIVOT your results using MAX with CASE that doesn't require joining the table back to itself:

select t.id1, t.id2, t.a, t.b, 
    max(case when t2.name = 'C1' then t2.vint end) c1,
    max(case when t2.name = 'C2' then t2.vstring end) c2,
    max(case when t2.name = 'C3' then t2.vdata end) c3
from tab1 t
    left join tab2 t2 on t.id1 = t2.id1 and t.id2 = t2.id2 
group by t.id1, t.id2, t.a, t.b