Обсуждение: Cross Tab Functions
<div class="Section1"><p class="MsoNormal"><span class="postbody1"><font face="Verdana" size="1"><span style="font-size:9.0pt;font-family:Verdana">HelloAll, </span></font></span><font face="Verdana" size="1"><span style="font-size:9.0pt;font-family:Verdana"><br/><span class="postbody1">I have used the cross tab function to setup tablesthat display months' data, however i need to display years data instead of the months.. so i would display 2006 asa column, 2007 as a colum and 2008 as a column. when i tried to modify the simple example of the cross tab (shown below)</span><br /><span class="postbody1">SELECT i.item_name::text As row_name, to_char(if.action_date, 'year')::text Asbucket, </span><br /><span class="postbody1">SUM(if.num_used)::integer As bucketvalue </span><br /><span class="postbody1">FROMinventory As i INNER JOIN inventory_flow As if </span><br /><span class="postbody1">ON i.item_id =if.item_id </span><br /><span class="postbody1">WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL) </span><br /><spanclass="postbody1">AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59' </span><br /><span class="postbody1">GROUPBY i.item_name, to_char(if.action_date, 'year'), date_part('year', if.action_date) </span><br /><spanclass="postbody1">ORDER BY i.item_name, date_part('year', if.action_date); </span><br /><br /><span class="postbody1">basicallyi changed the mon and the month to be year.. the system displayed something that i am not sureabout. is there a command that converts the year similar to the mon </span><br /><br /><span class="postbody1">Help again...Please</span></span></font><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial"></span></font></div>
Try using extract instead of to_char. date_trunc might also be of use. On Jun 20, 2008, at 8:05 AM, Chris Preston wrote: > Hello All, > I have used the cross tab function to setup tables that display > months' data, however i need to display years data instead of the > months.. so i would display 2006 as a column, 2007 as a colum and > 2008 as a column. when i tried to modify the simple example of the > cross tab (shown below) > SELECT i.item_name::text As row_name, to_char(if.action_date, > 'year')::text As bucket, > SUM(if.num_used)::integer As bucketvalue > FROM inventory As i INNER JOIN inventory_flow As if > ON i.item_id = if.item_id > WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL) > AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59' > GROUP BY i.item_name, to_char(if.action_date, 'year'), date_part > ('year', if.action_date) > ORDER BY i.item_name, date_part('year', if.action_date); > > basically i changed the mon and the month to be year.. the system > displayed something that i am not sure about. is there a command > that converts the year similar to the mon > > Help again...Please -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
I wrote a real simple trunc() function fashioned after oracles but used some simple rules to let me have modulo date_trunc. I don't have the code anymore. I might sit down and refigure it out... On Fri, Jun 20, 2008 at 8:54 PM, Decibel! <decibel@decibel.org> wrote: > Try using extract instead of to_char. date_trunc might also be of use. > > On Jun 20, 2008, at 8:05 AM, Chris Preston wrote: > >> Hello All, >> I have used the cross tab function to setup tables that display months' >> data, however i need to display years data instead of the months.. so i >> would display 2006 as a column, 2007 as a colum and 2008 as a column. when i >> tried to modify the simple example of the cross tab (shown below) >> SELECT i.item_name::text As row_name, to_char(if.action_date, >> 'year')::text As bucket, >> SUM(if.num_used)::integer As bucketvalue >> FROM inventory As i INNER JOIN inventory_flow As if >> ON i.item_id = if.item_id >> WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL) >> AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59' >> GROUP BY i.item_name, to_char(if.action_date, 'year'), date_part('year', >> if.action_date) >> ORDER BY i.item_name, date_part('year', if.action_date); >> >> basically i changed the mon and the month to be year.. the system >> displayed something that i am not sure about. is there a command that >> converts the year similar to the mon >> >> Help again...Please > > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > >