[SQL]UNPIVOT 多個欄位

2013-06-03

16495
0
SQL

SQL中如果要 UNPIVOT 2個欄位，要如何做呢? 關鍵在那裡呢?

先準備測試資料如下，

create table T (
no varchar(10),
yearx varchar(4),
z_type varchar(1),
A01 smallint,
B01 smallint,
A02 smallint,
B02 smallint,
A03 smallint,
B03 smallint
)
go

insert into T VALUES('Z01','2012','1',100,-10,200,30,50,60);
insert into T VALUES('Z01','2012','3',200,20,100,20,30,40);
insert into T VALUES('Z01','2013','3',300,30,10,50,70,30);

SELECT * FROM T;

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

SELECT no, yearx, z_type, A, B, x1, x2 
FROM 
   (SELECT *
   FROM T) p
UNPIVOT ( A FOR x1 IN (A01, A02, A03) )AS UnA
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)，就可以過濾出我們所需要的資料，如下，

SELECT no, yearx, z_type, A, B, x1, x2 
FROM 
   (SELECT *
   FROM T) p
UNPIVOT ( A FOR x1 IN (A01, A02, A03) )AS UnA
UNPIVOT ( B FOR x2 IN (B01, B02, B03) )AS UnB
WHERE RIGHT(x1, 1) = RIGHT(x2, 1)

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

SELECT *
, ROW_NUMBER() OVER(PARTITION BY no, yearx, z_type ORDER BY  no, yearx, z_type) AS SEQX
FROM
(
SELECT no, yearx, z_type, A, B, x1, x2 
FROM 
   (SELECT *
   FROM T) p
UNPIVOT ( A FOR x1 IN (A01, A02, A03) )AS UnA
UNPIVOT ( B FOR x2 IN (B01, B02, B03) )AS UnB
WHERE RIGHT(x1, 1) = RIGHT(x2, 1)
) T2