sql数据，列转行

Question

查询结果如下图，显示不太方便，希望将同一个地市昨天和前天的并排成一行，
比如701 20180224 8438 20180225 7631 这样显示

代码如下

select b.loc_id,to_char(a.finish_time,'yyyymmdd'),COUNT(1) 
from tpss.tpss_l_order_item_flag PARTITION(p201802) a,tpss.tb_loc_latn_info b
WHERE  a.finish_time >=trunc(SYSDATE-2,'dd')  --前天
AND a.finish_time <trunc(SYSDATE,'dd')    --昨天
and a.lan_id=b.lan_id
GROUP BY a.lan_id,to_char(a.finish_time,'yyyymmdd'),b.loc_id
ORDER BY b.loc_id,to_char(a.finish_time,'yyyymmdd');

请懂得朋友回答下，谢谢。

Answer 1

用了几个高端的函数

》点这里《

Answer 2

这个我建议可以用后端语言把数据查询出来以后再拼一个数组就可以了

Answer 3

固定的列转换比较简单，在原有的查询语句上包一层就行了，如：

with t_result as 
    select b.loc_id,a.finish_time, COUNT(1) as loc_count
    from tpss.tpss_l_order_item_flag PARTITION(p201802) a,tpss.tb_loc_latn_info b
    WHERE  a.finish_time >=trunc(SYSDATE-2,'dd')  --前天
    AND a.finish_time <trunc(SYSDATE,'dd')    --昨天
    and a.lan_id=b.lan_id
    GROUP BY a.lan_id,to_char(a.finish_time,'yyyymmdd'),b.loc_id
    ORDER BY b.loc_id,to_char(a.finish_time,'yyyymmdd')
select loc_id, max(finish_time_2), max(loc_count_1), max(finish_time_1), max(loc_count_1)
from (
    select loc_id, 
    decode(trunc(finish_time), trunc(sysdate-2), finish_time, null) as finish_time_2,
    decode(trunc(finish_time), trunc(sysdate-2), loc_count, null) as loc_count_2,
    decode(trunc(finish_time), trunc(sysdate-1), finish_time, null) as finish_time_1,
    decode(trunc(finish_time), trunc(sysdate-1), loc_count, null) as loc_count_1,
    from t_result
 ) group by loc_id