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 |
    |  2  |  9  |   'bb'   |'Smith' | 0 | 13/12 |

TAB2

   -------------------------------------------------------------------
   | NAME | ID1 | ID2 | SIGNEDBY | VSTRING | VINT |  VDATA  |  D | E |
   | 'C1' |  1  |  8  |   'aa'   |   NULL  |   1  |   NULL  | 'l'| 5 |
   | 'C2' |  1  |  8  |   'aa'   |  'sth'  | NULL |   NULL  | 'p'| 4 |
   | 'C3' |  1  |  8  |   'aa'   |   NULL  | NULL | 12/1/13 | 'q'| 5 |
   | 'C2' |  2  |  9  |   'bb'   |  'oth'  | NULL |   NULL  | 'p'| 4 |
   | 'C3' |  2  |  9  |   'bb'   |   NULL  | NULL |  1/1/11 | 'q'| 5 |

I need a query which will produce

TAB3

    ----------------------------------------------------
    | ID1 | ID2 |   A    | B |  C1  |  C2   |    c3   | 
    |  1  |  8  | 'John' | 9 |   1  | 'sth' | 12/1/13 | 
    |  2  |  9  | 'Smith'| 0 | NULL | 'oth' |  1/1/11 |

First I tried to create the TAB3 locally, insert data form the TAB1 to the TAB3 and then for each of the NAMEs I called "MERGE INTO Table". It was working correctly but too slow (more than 4 min). Then I tried a query like:

Select ID1, ID2, A, (Select VINT from TAB3 where Name - 'C1' and ....) 'C1',
     .... from TAB1

This also was working fine but still too slow. Then I come across pivot command but I didn't manage to write a working code.Is it possible to write a quick query for this problem (ideally one) ?

回答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

回答2:

Try:

select t.id1, t.id2, t.a, t.b, c1.vint c1, c2.vstring c2, c3.vdata c3
from tab1 t
left join tab2 c1 
     on t.id1=c1.id1 and t.id2=c1.id2 and t.signedby=c1.signedby and c1.name='C1'
left join tab2 c2
     on t.id1=c2.id1 and t.id2=c2.id2 and t.signedby=c2.signedby and c2.name='C2'
left join tab2 c3
     on t.id1=c3.id1 and t.id2=c3.id2 and t.signedby=c3.signedby and c3.name='C3'