oracle怎么设置转列 oracle的行转列
oracle 行转列 ，这个要怎么转
with t(cate_displayname,info_waritername,count) as (
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：域名申请、网络空间、营销软件、网站建设、南山网站维护、网站推广。
select '2014年','lizifeng',1 from dual
union all
select '历史沿革','wenqingwen',20 from dual
union all
select '领导讲话','lizifeng',3 from dual
)
SELECT cate_displayname,
nvl(lizifeng,0) as lizifeng,
nvl(wenqingwen,0) as wenqingwen
FROM t
PIVOT (
sum(count) ---- pivot_clause
FOR info_waritername ---- pivot_for_clause
IN ('lizifeng' as lizifeng,'wenqingwen' as wenqingwen) ---- pivot_in_clause
);
输出：
CATE_DISPLAYNAME LIZIFENG WENQINGWEN
------------------------ ---------- ----------
2014年 1 0
历史沿革 0 20
领导讲话 3 0
如何在oracle中进行列行转换
数据表示例：
假设要将name值作为行，course值作为列，转换后效果为：
对应的SQL如下：
方法1：使用表连接
SELECT DISTINCT a.name,
(SELECT score FROM grade b WHEREa.name=b.name AND b.course='语文') AS '语文',
(SELECT score FROM grade b WHEREa.name=b.name AND b.course='数学') AS '数学',
(SELECT score FROM grade b WHEREa.name=b.name AND b.course='英语') AS '英语'
FROM grade a
方法2：使用分组
SELECT name,
SUM(CASE course WHEN '语文' THEN score END) AS '语文',
SUM(CASE course WHEN '数学' THEN score END) AS '数学',
SUM(CASE course WHEN '英语' THEN score END) AS '英语'
FROM grade GROUP BY name
oracle的行列转换，怎么实现？
--行转列的3种方法,参考
--http: / / blog.sina.com.cn/s/blog_475839a50100s2q3.html
--1. UNION ALL
--适用范围：8i,9i,10g及以后版本
with tmp as(
select 123 as a, 456 as b, 789 as c from dual)
SELECT a 新列名 FROM tmp
UNION ALL
SELECT b FROM tmp
UNION ALL
SELECT c FROM tmp
--若空行不需要转换，只需加一个where条件，
--WHERE COLUMN IS NOT NULL 即可。
--2. MODEL
--适用范围：10g及以后
with tmp as(
select 123 as a, 456 as b, 789 as c from dual)
SELECT v 新列名 FROM tmp
MODEL
RETURN UPDATED ROWS
DIMENSION BY (0 AS n)
MEASURES ('xx' AS cn,'yyy' AS v,a,b,c)
RULES UPSERT ALL(
v[1] = a[0],
v[2] = b[0],
v[3] = c[0]
)
--3. COLLECTION
--适用范围：8i,9i,10g及以后版本
--要创建一个对象和一个集合：
CREATE OR REPLACE TYPE v_type AS OBJECT(v VARCHAR2(100));
CREATE OR REPLACE TYPE v_varr AS VARRAY(8) OF v_type;
with tmp as(
select 123 as a, 456 as b, 789 as c from dual)
SELECT t.v AS 新列名
FROM tmp,
TABLE(v_varr(v_type(tmp.a),v_type(tmp.b),v_type(tmp.c))) t;
oracle如何实现行转列
用union all
假设列名分别为 col1 cola colb...
select col1,cola
from tabname
where ...
union all
select col1,colb
from tabname
where ...
union all
select col1,colc
from tabname
where ...
union all
select col1,cold
from tabname
where ...
union all
select col1,cole
from tabname
where ...
union all
select col1,colf
from tabname
where ...
Oracle列转行，行转列
oracle下可以用函数decode处理:
select 产品名称,
sum(decode(季度,'第一季度',销售额,0)) 第一季度销售额,
sum(decode(季度,'第二季度',销售额,0)) 第二季度销售额,
sum(decode(季度,'第三季度',销售额,0)) 第三季度销售额,
sum(decode(季度,'第四季度',销售额,0)) 第四季度销售额,
from 表名
group by 产品名称;
新闻名称：oracle怎么设置转列 oracle的行转列
标题来源：http://cdiso.cn/article/hhogig.html
