动态构造,这个很容易懂吧
DECLARE
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT 门店 FROM TB T ORDER BY 门店;
BEGIN
V_SQL := 'SELECT 商品编码';
FOR V_XCLCK IN CURSOR_1 LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(门店,''' || V_XCLCK.门店 ||
''',销量,0)) AS ' || V_XCLCK.门店;
END LOOP;
V_SQL := V_SQL || ' FROM TB GROUP BY 商品编码';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE OR REPLACE VIEW TB2 AS ' || V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;
DECLARE
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT DISTINCT 门店 FROM TB T ORDER BY 门店;
BEGIN
V_SQL := 'SELECT 商品编码';
FOR V_XCLCK IN CURSOR_1 LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(门店,''' || V_XCLCK.门店 ||
''',销量,0)) AS ' || V_XCLCK.门店;
END LOOP;
V_SQL := V_SQL || ' FROM TB GROUP BY 商品编码';
--DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE OR REPLACE VIEW TB2 AS ' || V_SQL;
--DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;
你试试这个语句:select 商品编号,
max(decode(门店,a,销量)) 门店a,
max(decode(门店,b,销量)) 门店b,
max(decode(门店,c,销量)) 门店c
from 表
group by 商品编号;
--在代码里面拼接 也很方便的
--先查询SELECT DISTINCT 门店 FROM TB T ORDER BY 门店;返回所有的门店的集合ds
--然后拼接sql 和存储过程思路一样
string sql = "SELECT 商品编码";
for(int i = 0;i <= ds.table[0].rows.count;i++)
{
sql += ",SUM(DECODE(门店,'"+ds.table[0].rows[i][0]+"',1,0)) \"门店"+ds.table[0].rows[i][0]+"\"";
}
sql += "FROM TB GROUP BY 商品编码";