HELP WITH TRANSPOSE DATA

[acct098]

Here is the output from a select statement:

1 select FCAT, TC, TT, VCAT, BOC, ENTRY , ACEV, DESCR, DB, CR, DB2, CR2, DB3, CR3, DB4, CR4, EDAT
2 from fms_glfcat
3 where fcat in ( 'V ' , 'NV')
4 AND ENTRY LIKE '1243%'
5* ORDER BY ENTRY, FCAT
SQL> /

FC TC TT V BOC ENTR ACEV DESCR DB CR DB2 CR2 DB3 CR3 DB4 CR4 EDATE
-- -- -- - ---- ---- ---- ------------------------------ ---- ---- ---- ---- ---- ---- ---- ---- ---
NV VR 35 1243 SP03 REISSUE OF BEN PROCEEDS CHK 2113 2122 4650 490N 11-MAR-08
V VR 35 1243 SP03 REISSUE OF BEN PROCEEDS CHK 2113 2122 4650 490N 11-MAR-08 11-MAR-08

I'm trying to limit the out the number of output records by transposing the FCAT. I looked a number of examples but unable to determine if this can done.
Here is what the output should look like:

FCAT TC TT V BOC ENTR ACEV DESCR DB CR DB2 CR2 DB3 CR3 DB4 CR4 EDATE
------ -- -- - ---- ---- ---- --------------- ---- ---- ---- ---- ---- ---- ---- ---- ---
NV,V , VR 35 1243 SP03 TECK_EXAMPLE 2113 2122 4650 490N 11-MAR-08

There are 42 unique FCATs.

Any assistance would be greatly appreciated.

 

[SantaMufasa]

Acct,

Can you please provide us at least the sample records that would produce the output you want, above. Preferrably, you will post the SQL code to "CREATE TABLE fms_glfcat..." and "INSERT INTO fms_glfcat values (..." so that we can test and post code that does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[acct098]

Looking a few of your answers from other posts I think that a function would be appropriate(unsure about if a create or replace function would work).

I know what I want the output to look like.

Below is the sql that I think may work

User-defined function)
create or replace function fcat (glfcat_in varchar2)
ENTRY varchar2 is (my thought:TC||TT||VCAT||BOC||ENTRY||ACEV||DESCR||DB||CR||DB2||CR2||DB3||CR3||DB4||CR4||EDATE)
glfcat_in varchar2(100);
begin
for x in (select entry
from fms_glfcat
where glfcat_in = not sure about this
order by entry) loop
glfcat_in := entry;
end loop;
end;
/

 

[acct098]

CREATE TABLE FMS_GLFCAT (
TC VARCHAR2(02),
TT VARCHAR2(02),
FCAT VARCHAR2(02),
VCAT VARCHAR2(01),
BOC VARCHAR2(04),
ENTRY VARCHAR2(04),
ACEV VARCHAR2(04),
DESCR VARCHAR2(30),
DB VARCHAR2(04),
CR VARCHAR2(04),
DB2 VARCHAR2(04),
CR2 VARCHAR2(04),
DB3 VARCHAR2(04),
CR3 VARCHAR2(04),
DB4 VARCHAR2(04),
CR4 VARCHAR2(04),
EDATE DATE,
FY VARCHAR2(02))

 

[acct098]

INSERT INTO FMS_GLFCAT VALUES(
'VR',
'35',
'NV',
'G',
'2104',
'1243',
'SP03',
'test ',
'2113',
'2122',
'4650',
'490N',
'2113',
'2122',
'4650',
'490N',
'11-MAR-08',
'08');




INSERT INTO FMS_GLFCAT VALUES(
'VR',
'35',
'V',
'G',
'2104',
'1243',
'SP03',
'test ',
'2113',
'2122',
'4650',
'490N',
'2113',
'2122',
'4650',
'490N',
'11-MAR-08',
'08');

 

[SantaMufasa]

Acct,

Please forgive my dimness regarding your question, but if your data look as they do, and your proposed function behaves as it does, they how different are the following results from what you want?:

select
TC||TT||VCAT||BOC||ENTRY||ACEV||DESCR||DB||CR||DB2||CR2||DB3||CR3||DB4||CR4||EDATE entry
from fms_glfcat;

ENTRY
------------------------------------------------------------------
VR35G21041243SP03test    211321224650490N211321224650490N11-MAR-08
VR35G21041243SP03test    211321224650490N211321224650490N11-MAR-08

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[acct098]

I'm trying to get both fundcat on one line. All of the remaining fields in the record are the same (this should be grouped). I typed the output in the original post. I could not get the function or SQL statement to produce the desired output.