Re: About functionality like pivot-table using SQL

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 16 Oct 2006 04:37:28 -0700
Message-ID: <1160998648.396657.128020@i42g2000cwa.googlegroups.com>

Sameer wrote:
> Dear All.
> Please access the link of a documnet:
> http://docs.google.com/View?docid=dhntd3vh_0gmwgmq
> A 'query-output' is given.
> I have pasted this output in Excel and using it's Pivot table feature
> obtained an output as 'Expected-Output' given.
> How can I achieve this using SQL?
> If you suppose that it a RTFM question just ignore this.
> I am ready to work on this problem but any guidelines are most welcome.
> -Sameer

You will need to use GROUP BY and DECODE to obtain the desired results.  Something to experiement with:
SELECT
  MODULE,
  DECODE(UPPER(AFFECTEDAREATYPE),'INTELLIVIEW REPORT',ISSUE_COUNT,0) "INTELLIVIEW REPORT",

  DECODE(UPPER(AFFECTEDAREATYPE),'MASTER',ISSUE_COUNT,0) "MASTER",
  DECODE(UPPER(AFFECTEDAREATYPE),'REPORT',ISSUE_COUNT,0) "REPORT",
  DECODE(UPPER(AFFECTEDAREATYPE),'TRANSACTION',ISSUE_COUNT,0)

"TRANSACTION",
  DECODE(UPPER(AFFECTEDAREATYPE),'UTILITIES',ISSUE_COUNT,0) "UTILITIES" FROM
  MY_TABLE; Obviously, there is no GROUP BY in the above, so as written it will not give the exact results that you are wanting, without the GROUP BY. Also, you must know the possible values of the AFFECTEDAREATYPE when the query is constructed.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Oct 16 2006 - 06:37:28 CDT