有朋友問「​​如何直接unpivot成2個欄位​​」，如下所示，

​​​​


先準備測試資料如下，

​​view source​​​ ​​print​​​​?​​

​​01​​

​​create​​​​table​​​​T ( ​​


​​02​​

​​no​​​​varchar​​​​(10), ​​


​​03​​

​​yearx ​​​​varchar​​​​(4), ​​


​​04​​

​​z_type ​​​​varchar​​​​(1), ​​


​​05​​

​​A01 ​​​​smallint​​​​, ​​


​​06​​

​​B01 ​​​​smallint​​​​, ​​


​​07​​

​​A02 ​​​​smallint​​​​, ​​


​​08​​

​​B02 ​​​​smallint​​​​, ​​


​​09​​

​​A03 ​​​​smallint​​​​, ​​


​​10​​

​​B03 ​​​​smallint​​


​​11​​

​​) ​​


​​12​​

​​go ​​


​​13​​



​​14​​

​​insert​​​​into​​​​T ​​​​VALUES​​​​(​​​​'Z01'​​​​,​​​​'2012'​​​​,​​​​'1'​​​​,100,-10,200,30,50,60); ​​


​​15​​

​​insert​​​​into​​​​T ​​​​VALUES​​​​(​​​​'Z01'​​​​,​​​​'2012'​​​​,​​​​'3'​​​​,200,20,100,20,30,40); ​​


​​16​​

​​insert​​​​into​​​​T ​​​​VALUES​​​​(​​​​'Z01'​​​​,​​​​'2013'​​​​,​​​​'3'​​​​,300,30,10,50,70,30); ​​


​​17​​



​​18​​

​​SELECT​​​​* ​​​​FROM​​​​T;​​


​​​​


因為原本欄位A01, A02, A03要放到欄位A，B01, B02, B03要放到欄位B，所以需要2個UNPIVOT，如下，

​​view source​​​ ​​print​​​​?​​

​​1​​

​​SELECT​​​​no​​​​, yearx, z_type, A, B, x1, x2 ​​


​​2​​

​​FROM​​


​​3​​

​​​​​​(​​​​SELECT​​​​* ​​


​​4​​

​​​​​​FROM​​​​T) p ​​


​​5​​

​​UNPIVOT ( A ​​​​FOR​​​​x1 ​​​​IN​​​​(A01, A02, A03) )​​​​AS​​​​UnA ​​


​​6​​

​​UNPIVOT ( B ​​​​FOR​​​​x2 ​​​​IN​​​​(B01, B02, B03) )​​​​AS​​​​UnB​​


​​​​


上面的輸出筆數為27筆，而我們可以發現，x1, x2欄位中，A01 搭配 B01，A02 要搭配 B02，A03 要搭配 B03，如下圖所示，

​​​​


所以我們需要再加入 關鍵的 WHERE 條件， RIGHT(x1, 1) = RIGHT(x2, 1)，就可以過濾出我們所需要的資料，如下，

​​view source​​​ ​​print​​​​?​​

​​1​​

​​SELECT​​​​no​​​​, yearx, z_type, A, B, x1, x2 ​​


​​2​​

​​FROM​​


​​3​​

​​​​​​(​​​​SELECT​​​​* ​​


​​4​​

​​​​​​FROM​​​​T) p ​​


​​5​​

​​UNPIVOT ( A ​​​​FOR​​​​x1 ​​​​IN​​​​(A01, A02, A03) )​​​​AS​​​​UnA ​​


​​6​​

​​UNPIVOT ( B ​​​​FOR​​​​x2 ​​​​IN​​​​(B01, B02, B03) )​​​​AS​​​​UnB ​​


​​7​​

​​WHERE​​​​RIGHT​​​​(x1, 1) = ​​​​RIGHT​​​​(x2, 1)​​


​​​​


如果要加上序號的話，就加入 ROW_NUMBER 就可以了，如下，

​​view source​​​ ​​print​​​​?​​

​​01​​

​​SELECT​​​​* ​​


​​02​​

​​, ROW_NUMBER() OVER(PARTITION ​​​​BY​​​​no​​​​, yearx, z_type ​​​​ORDER​​​​BY​​​​no​​​​, yearx, z_type) ​​​​AS​​​​SEQX ​​


​​03​​

​​FROM​​


​​04​​

​​( ​​


​​05​​

​​SELECT​​​​no​​​​, yearx, z_type, A, B, x1, x2 ​​


​​06​​

​​FROM​​


​​07​​

​​​​​​(​​​​SELECT​​​​* ​​


​​08​​

​​​​​​FROM​​​​T) p ​​


​​09​​

​​UNPIVOT ( A ​​​​FOR​​​​x1 ​​​​IN​​​​(A01, A02, A03) )​​​​AS​​​​UnA ​​


​​10​​

​​UNPIVOT ( B ​​​​FOR​​​​x2 ​​​​IN​​​​(B01, B02, B03) )​​​​AS​​​​UnB ​​


​​11​​

​​WHERE​​​​RIGHT​​​​(x1, 1) = ​​​​RIGHT​​​​(x2, 1) ​​


​​12​​

​​) T2​​


​​​​