Help with linking tables and grouping values into columns [message #218685] |
Fri, 09 February 2007 05:21  |
matpj
Messages: 115 Registered: March 2006 Location: London, UK
|
Senior Member |
|
|
Hi all,
I have a table, NBI_PRT_FACTS in the following format
Project_ID Fact_Date ETC_QTY PERIOD_NAME
5000001 15/02/2007 5 FEB07
5000001 16/02/2007 5 FEB07
5000001 12/03/2007 5 MAR07
5000001 20/04/2007 5 APR07
5000002 15/02/2007 5 FEB07
5000002 16/02/2007 5 FEB07
5000002 17/02/2007 5 MAR07
5000002 18/02/2007 5 MAR07
5000002 02/03/2007 5 MAR07
5000002 03/03/2007 5 MAR07
5000002 04/03/2007 5 MAR07
I need to be able to sum up the ETC_QTY for each project by PERIOD_NAME in column format.
the results above would become:
PROJECT_ID FEB07 MAR07 APR07
5000001 10 5 5
5000002 10 25 0
can someone please help me with this, as I cant think how to do it.
Thanks in advance,
Matt
|
|
|
Re: Help with linking tables and grouping values into columns [message #218688 is a reply to message #218685] |
Fri, 09 February 2007 05:34   |
dhananjay
Messages: 635 Registered: March 2002 Location: Mumbai
|
Senior Member |
|
|
hi,
SELECT PROJECT_ID
,SUM(DECODE(PERIOD_NAME,'FEB07',ETC_QTY))FEB07
,SUM(DECODE(PERIOD_NAME,'MAR07',ETC_QTY))MAR07
,SUM(DECODE(PERIOD_NAME,'APR07',ETC_QTY))APR07
FROM TEST
GROUP BY PROJECT_ID
regards,
|
|
|
Re: Help with linking tables and grouping values into columns [message #218695 is a reply to message #218688] |
Fri, 09 February 2007 06:11  |
matpj
Messages: 115 Registered: March 2006 Location: London, UK
|
Senior Member |
|
|
thanks for thats brilliant.
although, is it possible to have the query automatically name the columns?
I want to be able to run this query at any time, and it will pick up the period names in the list, and turn them to columns - rather than me having to title each column manually in the SQL...
does that make sense?
I would never be more than 12 months in advance.
thanks,
Matt
|
|
|