Re: Multiple column values display on one line

"MS" <msandle_at_yahoo.com> a écrit dans le message de news: 1141411946.756269.32010_at_p10g2000cwp.googlegroups.com...
|I have a table with sales person id and sales region, there are
| multiple rows for each person.
| I have to create an output with person id and all regions on one line.
| Can I do this in one sql statement?
|
| I'm including simplified table with data.
| 1. Sample output trying to create:
|
| ID REG1 REG2 REG3 REG4
| 1 5 9 20 28
| 2 2 15
|
|
| 2. sample table:
| CREATE TABLE ID_TEST
| (
| ID NUMBER(2) NOT NULL,
| REGION VARCHAR2(2 BYTE) NOT NULL
| )
| LOGGING
| NOCOMPRESS
| NOCACHE
| NOPARALLEL
| MONITORING;
|
| CREATE UNIQUE INDEX PK_ID_TEST ON ID_TEST
| (ID, REGION)
| LOGGING
| NOPARALLEL;
|
| ALTER TABLE ID_TEST ADD (
| CONSTRAINT PK_ID_TEST PRIMARY KEY (ID, REGION));
|
| 3. sample data:
| Insert into ID_TEST
| (ID, REGION)
| Values
| (1, '20');
| Insert into ID_TEST
| (ID, REGION)
| Values
| (1, '28');
| Insert into ID_TEST
| (ID, REGION)
| Values
| (1, '5');
| Insert into ID_TEST
| (ID, REGION)
| Values
| (1, '9');
| Insert into ID_TEST
| (ID, REGION)
| Values
| (2, '15');
| Insert into ID_TEST
| (ID, REGION)
| Values
| (2, '2');
| COMMIT;
|

Is this exam time?

This is a FAQ.
Search a little bit on AskTom you'll get a solution. You can also search by yourself.
Hint: max, decode, row_number

Regards
Michel Cadot Received on Fri Mar 03 2006 - 13:22:18 CST