Question

select
extract(year from datetimestamp ) Yr,extract(month from datetimestamp) Mn,
c.weekday_of_month wk, a.aircraft_type,count( a.aircraft_type)  from fcm_bv.Flights b
join fcm_bv.Fleet a on  b.aircraftid=a.tail 
 join SYS_CALENDAR.CALENDAR c
 on cast(b.datetimestamp AS DATE FORMAT  YYYY-MM-DD ) = cast(c.calendar_date AS DATE FORMAT  YYYY-MM-DD )
where cast(datetimestamp as date) >=  2011-09-01 
and cast(datetimestamp as date) <=  2011-09-30   order by wk
group by Yr,Mn,wk,a.fleet,a.aircraft_type

While Running above Query I am getting out put like this

Yr      Mn  wk  AIRCRAFT_TYPE   Count(AIRCRAFT_TYPE)
2011    9   1   B737-700        1744
2011    9   1   B737-800        131
2011    9   1   B737-800W       2711
2011    9   1   B737-8BK        180
2011    9   1   B737-700W       329

但我需要以下格式的产出:

Yr   Mn    wk    B737-700    B737-800  B737-800W   B737-8BK    B737-700W      
2011 9      1      1744       131          2711        180         329

谁能帮助我

Answer 1

过去,在我需要做这项工作的时候,这片花旗反对一个分散的、可选择的类别,而以下的Kall为我提供了很好的服务:

SELECT EXTRACT(YEAR FROM b.datetimestamp) AS Yr
     , EXTRACT(MONTH FROM b.datetimestamp) AS Mn
     , C.weekday_of_month
     , COUNT(CASE WHEN a.aircraft_type =  B737-700  THEN a.aircraft_type ELSE NULL END) AS B737-700
     , COUNT(CASE WHEN a.aircraft_type =  B737-800  THEN a.aircraft_type ELSE NULL END) AS B737-800
     , /* Other Known Aircrafts */
     , COUNT(CASE WHEN a.aircrat_type NOT IN ( <list of known aircraft types> ) THEN a.aircraft_type ELSE NULL END) AS Uncategorized_Aircraft
FROM fcm_bv.Flights b
join fcm_bv.Fleet a on  b.aircraftid=a.tail 
 join SYS_CALENDAR.CALENDAR c
 on cast(b.datetimestamp AS DATE FORMAT  YYYY-MM-DD ) = cast(c.calendar_date AS DATE FORMAT  YYYY-MM-DD )
WHERE cast(datetimestamp as date) >=  2011-09-01 
  AND cast(datetimestamp as date) <=  2011-09-30   order by wk
GROUP BY Yr,Mn,wk,a.fleet

如果你不得不针对不断变化的一类人 p,则最好把雕刻留给MS Excel或BI选择工具。

希望这一帮助。

友情链接