甲骨文转换行到列 [英] Oracle Convert Rows to Columns

问题描述

我已经如下因素DATAOUT从我的查询放在

I have folowing Dataout put from my query

**Date**    **HIGH**  **LOW**       **IMAGE**      **TYPE**
1/28/2012     69         42          1.jpg           SUN
1/29/2012     70         42          2.jpg           MSUN

我想这个输出转换为

I want to Convert this Output into

**1/28/2012**       **1/29/2012**
1.jpg                    2.jpg    
Sun                       MSUN
69                         72  
42                         42

下面是我的查询

SELECT 
   W_DATE,HIGH, LOW, 
   W_TYPE, IMAGE
FROM WEATHER
ORDER BY W_DATE ASC

和我也有多个日期行，我想只显示4日期和他们应该改变的时候系统日期chanaged

And Also I have multiple dates in rows i want to display only 4 dates and they should change when system date is chanaged

推荐答案

关于做好一切准备如何从行甲骨文中得到列你可以在这里读到：

About all the possibilities how get from rows to columns in oracle you can read up here:

http://www.dba-oracle.com/t_converting_rows_columns.htm

我没有看到，从查看数据库点直接的解决方案 - 会建议做应用端的格式，否则它可能看起来像跛脚，因为这：

I don't see a straight forward solution for that from database point of view - would suggest to do the formatting on application side, otherwise it could look as lame as this:

SELECT
   to_char(w1.w_Date,'MM/DD/YYYY'), to_char(w2.w_Date,'MM/DD/YYYY'), 
   to_char(w3.w_Date,'MM/DD/YYYY'), to_char(w4.w_Date,'MM/DD/YYYY')
FROM 
 (select * from weather where w_date = trunc(sysdate)) w1,
 (select * from weather where w_date = trunc(sysdate) + 1) w2,
 (select * from weather where w_date = trunc(sysdate) + 2) w3,
 (select * from weather where w_date = trunc(sysdate) + 3) w4
UNION ALL
SELECT
   w1.image,  w2.image, w3.image , w4.image
FROM 
 (select * from weather where w_date = trunc(sysdate)) w1,
 (select * from weather where w_date = trunc(sysdate) + 1) w2,
 (select * from weather where w_date = trunc(sysdate) + 2) w3,
 (select * from weather where w_date = trunc(sysdate) + 3) w4
UNION ALL
SELECT
   w1.w_type,  w2.w_type, w3.w_type , w4.w_type
FROM 
 (select * from weather where w_date = trunc(sysdate)) w1,
 (select * from weather where w_date = trunc(sysdate) + 1) w2,
 (select * from weather where w_date = trunc(sysdate) + 2) w3,
 (select * from weather where w_date = trunc(sysdate) + 3) w4
UNION ALL
SELECT
   to_char(w1.high),  to_char(w2.high), to_char(w3.high) , to_char(w4.high)
FROM 
 (select * from weather where w_date = trunc(sysdate)) w1,
 (select * from weather where w_date = trunc(sysdate) + 1) w2,
 (select * from weather where w_date = trunc(sysdate) + 2) w3,
 (select * from weather where w_date = trunc(sysdate) + 3) w4
UNION ALL
SELECT
   to_char(w1.low),  to_char(w2.low), to_char(w3.low) , to_char(w4.low)
FROM 
 (select * from weather where w_date = trunc(sysdate)) w1,
 (select * from weather where w_date = trunc(sysdate) + 1) w2,
 (select * from weather where w_date = trunc(sysdate) + 2) w3,
 (select * from weather where w_date = trunc(sysdate) + 3) w4;
/

这篇关于甲骨文转换行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！