CREATE TABLE SalesAgg ( year INTEGER ,q1 INTEGER ,q2 INTEGER ,q3 INTEGER ,q4 INTEGER ); insert into SalesAgg values(2004,20,30,15,10); insert into SalesAgg values(2005,18,40,12,27);
Select * from SalesAgg
YEAR Q1 Q2 Q3 Q4
---- -- -- -- --
2004 20 30 15 10
2005 18 40 12 27
想把数据转成下面的样子
YEAR QUARTER RESULTS
---- ------- -------
2004 1 20
2004 2 30
2004 3 15
2004 4 10
2005 1 18
2005 2 40
2005 3 12
2005 4 27
列转换成行的SQL
SELECT Year, Quarter, Results
FROM SalesAgg AS S,
LATERAL(VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(Quarter, Results);LATERAL的地方可以换成TABLE,结果一样,其目的都是通过VALUES值来组合成一个临时表Q在SQL中使用
另外一个使用TABLE的例子
SELECT id ANSWER
,salary AS sal
,comm AS com
,combo
,typ
FROM staff
,TABLE (
VALUES
(salary,'SAL')
,(comm ,'COM')
) AS tab(combo, typ)
WHERE id < 40
ORDER BY id
,typ;
行转列使用示例
CREATE OR REPLACE PROCEDURE DB2INST1.TEST(OUT out_msg VARCHAR(4000))
SPECIFIC DB2INST1.TEST
MODIFIES SQL DATA
NOT DETERMINISTIC
NULL CALL
LANGUAGE SQL EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE v_col1s VARCHAR(4000);
DECLARE v_sql VARCHAR(4000);
SELECT SUBSTR(XMLCAST(XMLGROUP(',' || col1 AS a ORDER BY col1) as varchar(4000)),2) into v_col1s FROM aaa;
SET out_msg = 'SELECT * FROM aaa where col1 in (' || TRIM(v_col1s) || ')';
END