[SQL]使用UNPIVOT來取代CASE WHEN
有朋友問「如果column name與另外一個column的內容是有關聯的，如果通過sql 查問」，直接看圖比較清楚，如下，
測試Script如下，
USE tempdb
GO
--drop table #t1
CREATE TABLE #t1
(
client VARCHAR(10),
pay_level INT,
pay_lv_1_cost INT,
pay_lv_2_cost INT,
pay_lv_3_cost int
);
INSERT INTO #t1 ( client ,
pay_level ,
pay_lv_1_cost ,
pay_lv_2_cost ,
pay_lv_3_cost
)
VALUES ( 'client1' , -- client - varchar(10)
1, -- pay_level - int
10 , -- pay_lv_1 - int
12 , -- pay_lv_2 - int
14 -- pay_lv_3 - int
)
INSERT INTO #t1 ( client ,
pay_level ,
pay_lv_1_cost ,
pay_lv_2_cost ,
pay_lv_3_cost
)
VALUES ( 'client2' , -- client - varchar(10)
3, -- pay_level - int
21 , -- pay_lv_1 - int
22 , -- pay_lv_2 - int
23 -- pay_lv_3 - int
)
INSERT INTO #t1 ( client ,
pay_level ,
pay_lv_1_cost ,
pay_lv_2_cost ,
pay_lv_3_cost
)
VALUES ( 'client3' , -- client - varchar(10)
2, -- pay_level - int
30 , -- pay_lv_1 - int
32 , -- pay_lv_2 - int
33 -- pay_lv_3 - int
)
要的結果如下，
--使用CASE WHEN
SELECT client,
CASE pay_level
WHEN 1 THEN pay_lv_1_cost
WHEN 2 THEN pay_lv_2_cost
WHEN 3 THEN pay_lv_3_cost
ELSE -99 END AS PaylvCost
FROM #t1;
所以如果不想使用CASE WHEN的話，可以改用 UNPIVOT ，如下，
--使用UNPIVOT + WHERE
SELECT *
FROM
(SELECT *
FROM #t1) p
UNPIVOT
(PaylvCost FOR pay_lv IN
(pay_lv_1_cost, pay_lv_2_cost, pay_lv_3_cost)
)AS unpvt
--將CASE WHEN的判斷，改成 CAST(RIGHT(pay_lv, 1) AS INT) = pay_level;
WHERE CAST(substring(pay_lv, 8, 1) AS INT) = pay_level;
2013/09/02 補充使用動態的方式，
--使用動態
DECLARE @in_columns VARCHAR (MAX)
SELECT @in_columns = COALESCE (@in_columns + ',[' + 'pay_lv_' + RTRIM(T.pay_level) + '_cost]', '[' + 'pay_lv_' + RTRIM(T.pay_level) + '_cost]')
FROM (SELECT DISTINCT pay_level FROM #t1) T
PRINT @in_columns;
EXEC('SELECT * FROM
(SELECT *
FROM #t1) p
UNPIVOT
(PaylvCost FOR pay_lv IN
(' + @in_columns + ')
)AS unpvt
--取出 pay_lv_1_cost中的數值
WHERE CAST(substring(pay_lv, 8, len(pay_lv) - 7 - CHARINDEX(''_'', REVERSE(pay_lv)) ) AS INT) = pay_level;');
不知其他先進是否有其他的方法呢?
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
