Обсуждение: 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
>
>
>