连接表上的数据透视表SQL Server [英] Pivot on joined tables SQL Server

问题描述

我有两个带有数据的表

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 |

首先，我尝试在本地创建TAB3，将数据从TAB1插入TAB3，然后为每个我称为"MERGE INTO Table"的名称创建.它工作正常，但速度太慢(超过4分钟).然后我尝试了如下查询:

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) ?

推荐答案

以下是使用MAX和CASE的结果PIVOT的另一种选择，不需要将表重新连接到自身:

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

这篇关于连接表上的数据透视表SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！