Ⅰ Oracle sql如何實現「可判斷的行轉列」的方法
如果確定只有最多三個項列那麼可以這樣
select 組號,單位,項一 項目號
,項一名 項目名
from tab where 項一 is not null
union all
select 組號,單位,項二,項二名 from tab where 項二 is not null
union all
select 組號,單位,項三,項三名 from tab where 項三 is not null
;
如採納加分
Ⅱ oracle行轉列寫法，麻煩大家幫忙寫個sql，謝謝
可以用wm_concat函數先把數據變成行顯示，然後再通過截取來顯示具體的月份，wm_concat轉換如下
selectcompay_namecn,wm_concat(income)ic
from(selectcompay_name,sum(income)income,substr(time,1,6)time
fromincome
---wheretime>=start_monthandtime<=end_month
groupbycompay_name,substr(time,1,6)
orderbycompay_name,substr(time,1,6))
groupbycompay_name;
Ⅲ ORAClE sql如何實現行轉列
如果「站名」、「條碼」、「時間」都是一樣的話，可以這么寫：
with
t_temp as (select row_number() over (partition by station_name order by param_name asc) id, t.* from t),
t_temp1 as (select * from t_temp where id = 1),
t_temp2 as (select * from t_temp where id = 2),
t_temp3 as (select * from t_temp where id = 3)
select '站名' col1, '條碼' col2, t_temp1.參數名 col3, t_temp2.參數名 col4, t_temp3.參數名 col5, '時間' col6
from t_temp1, t_temp2, t_temp3
where t_temp1.站名 = t_temp2.站名
and t_temp2.站名 = t_temp3.站名
union all
select t_temp1.站名, t_temp1.條碼, to_char(t_temp1.數值), to_char(t_temp2.數值), to_char(t_temp3.數值), to_char(t_temp1.時間)
from t_temp1, t_temp2, t_temp3
where t_temp1.站名 = t_temp2.站名
and t_temp2.站名 = t_temp3.站名
Ⅳ oracle 如何將 行轉換為列 (具體如下,求sql)
WITHAAS(SELECTID_F,LINE_F,STAGE_F,UNITSTAGE_F,CAST(CHILDSTAGE_FASVARCHAR(100))ASCHILDSTAGE_F,PARENTID_FFROM表名WHEREPARENTID_FISNULL
UNIONALL
SELECT表名.ID_F,表名.LINE_F,表名.STAGE_F,表名.UNITSTAGE_F,CAST(A.CHILDSTAGE_F+表名.CHILDSTAGE_FASVARCHAR(100))ASCHILDSTAGE_F,表名.PARENTID_F
FROM表名JOINAON表名.PARENTID_F=A.ID_F)
SELECTLINE_F,STAGE_F,UNITSTAGE_F,CHILDSTAGE_F
FROMA
WHEREPARENTID_FISNOTNULL