Display rows as column header

[rk68]

Hi,

Below are data from the table.

Campaign Code, Campaign Field, Campaign Field Description
1954, Field1, Mobile
1954, Field2, Email
1954, Field3, Name
2005, Field1, Website
2005, Field2, Mobile

I want to display the field "Campaign Field Description" as Column header when a particular Campaign Code.
for e.g. if I want to show "Campaign Field Description" for Campaign Code 1954 then the output should be as

1954 Mobile Email Name

How can this be done. I tried using Pivot but wasn't successful.

TIA,
Raj

 

[Andrzejek]

Do some digging on Oracle's LISTAGG


---- Andy

There is a great need for a sarcasm font.

 

[rk68]

Thanks Andy for the repy.
LISTAGG did help to some extent.

 

[Beilstwh]

Listagg returns exactly what you asked for

CREATE TABLE CAMPAIGN
(
  CAMPAIGN_CODE   NUMBER(4),
  CAMPAIGN_FIELD  VARCHAR2(10 BYTE),
  CAMPAIGN_DES    VARCHAR2(20 BYTE)
);


Insert into CAMPAIGN
   (CAMPAIGN_CODE, CAMPAIGN_FIELD, CAMPAIGN_DES)
 Values
   (2005, 'FIELD2', 'MOBILE');
Insert into CAMPAIGN
   (CAMPAIGN_CODE, CAMPAIGN_FIELD, CAMPAIGN_DES)
 Values
   (2005, 'FIELD1', 'WEBSITE');
Insert into CAMPAIGN
   (CAMPAIGN_CODE, CAMPAIGN_FIELD, CAMPAIGN_DES)
 Values
   (1954, 'FIELD3', 'NAME');
Insert into CAMPAIGN
   (CAMPAIGN_CODE, CAMPAIGN_FIELD, CAMPAIGN_DES)
 Values
   (1954, 'FIELD2', 'EMAIL');
Insert into CAMPAIGN
   (CAMPAIGN_CODE, CAMPAIGN_FIELD, CAMPAIGN_DES)
 Values
   (1954, 'FIELD1', 'MOBILE');
COMMIT;

 SELECT Campaign_code,
         LISTAGG (Campaign_des, ' ') WITHIN GROUP (ORDER BY Campaign_field)    Des
    FROM Campaign
GROUP BY Campaign_code;

1954	Mobile Email Name
2005	Website Mobile

Bill
Lead Application Developer
New York State, USA