beginers question

Depends on the database platform. If you are running MS Access, it can be
easily performed with a pivot SQL expression (cross-tab query). But something
like SQL Server doesn't (out of the box) allow for such a query, they have
to be constructed through a case statement. Not sure if Oracle offers a
quick solution or not, I have always replicated the SQL Server solution in
Oracle.

Someone sent me a link once to a 'crosstab' stored procedure for SQL Server...I've
got the SP on my server at home, I'll post it when I get home tonight...unless
someone else has a link to a similar (or the same) stored proc. It basically
accepted a table name and the 'across' and 'by' fields and built the SQL
statement on the fly...just a case statement.

Chris

"ishay" <ishay@ovdimnet.co.il> wrote:
>
>i'm holding in a table :
>
> employee_id | month | working hours
>
>and i'd like to query and get a result like:
>
>employee_id | january | february | .......
>1 | 55 | 64 | .......
>2 | 24 | 86 | .......
>
>(never mind the month name, it can be 1,2,3,...12)
>
>how do i do it ?
>
>Thanks
>ishay
>
>

Thanks a lot ! exactly what i was looking for.

"Chris Hylton" <cchylton@hotmail.com> wrote:
>
>In Access, just use the cross-tab query wizard (create new query) and it'll
>do it for you...I don't recall the exact statements to build a cross-tab
>in Access directly from typing in the SQL.
>
>Guess I could be more helpful and post an actual example of a CASE statement
>in SQL Server.
>
>SELECT employee_id,
> SUM(CASE month WHEN 1 THEN working_hours ELSE 0 END) AS January,
> SUM(CASE month WHEN 2 THEN working_hours ELSE 0 END) AS February,
> SUM(CASE month WHEN 3 THEN working_hours ELSE 0 END) AS March,
> ...
> SUM(CASE month WHEN 12 THEN working_hours ELSE 0 END) AS December
>FROM <tablename>
>GROUP BY employee_id
>
>Hope this helps,
>Chris
>
>"Chris Hylton" <cchylton@hotmail.com> wrote:
>>
>>Depends on the database platform. If you are running MS Access, it can
>be
>>easily performed with a pivot SQL expression (cross-tab query). But something
>>like SQL Server doesn't (out of the box) allow for such a query, they have
>>to be constructed through a case statement. Not sure if Oracle offers
a
>>quick solution or not, I have always replicated the SQL Server solution
>in
>>Oracle.
>>
>>Someone sent me a link once to a 'crosstab' stored procedure for SQL Server...I've
>>got the SP on my server at home, I'll post it when I get home tonight...unless
>>someone else has a link to a similar (or the same) stored proc. It basically
>>accepted a table name and the 'across' and 'by' fields and built the SQL
>>statement on the fly...just a case statement.
>>
>>Chris
>>
>>"ishay" <ishay@ovdimnet.co.il> wrote:
>>>
>>>i'm holding in a table :
>>>
>>> employee_id | month | working hours
>>>
>>>and i'd like to query and get a result like:
>>>
>>>employee_id | january | february | .......
>>>1 | 55 | 64 | .......
>>>2 | 24 | 86 | .......
>>>
>>>(never mind the month name, it can be 1,2,3,...12)
>>>
>>>how do i do it ?
>>>
>>>Thanks
>>>ishay
>>>
>>>
>>
>