[SQL]UNPIVOT 多個欄位

有朋友問「如何直接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