Home » SQL & PL/SQL » SQL & PL/SQL » A Query for Seating Arrangement
A Query for Seating Arrangement [message #652857] |
Tue, 21 June 2016 02:07 |
|
Let's take an example of Sitting Arrangement in a building.
Assume a Building has 4 or more Floors.
All the Floor members are stored in sequence in comma separated form as shown below. (Assume it's an upload format from an external flat file).
Empty Commas are the Empty Places in that floor.
I have to Make a Query that shows Floor, Seat and a Report Statement. Output should be as below.
Seat Number is 4B suffixed with Floor Number followed by W and position of the person separated by commas.
4B||< Floor Number >||W||< Position of Person >
Position of Person will be dynamic depending the Total Comma Separated Values in the string.
below is the sample table and data
CREATE TABLE TAB_ARRANGEMENT
(
FLOOR NUMBER,
PERSONS_IN_ORDER VARCHAR2(200 BYTE)
);
/
SET DEFINE OFF;
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(1, 'KRISHNA,GOPAL,ARJUN,RAKHI,NISHA,,,,,,DEBA');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(2, 'SAKTHI,RAKESH,,,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(3, ',,,,,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(4, 'ARUN,PRADEEP,ABHI,,,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(5, 'RAGHAV,KARTIK,SAMULE,TINTIN,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(6, ',,,,,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(7, ',,,,,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(8, 'RAKESH.G,,,,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(9, 'GEETHA,PRIYA,NEHA,HEMA,,,,,,');
Insert into TAB_ARRANGEMENT
(FLOOR, PERSONS_IN_ORDER)
Values
(10, 'PRASAD,,,,,,,,,');
COMMIT;
Javed
-
Attachment: screen1.png
(Size: 102.56KB, Downloaded 1437 times)
[Updated on: Tue, 21 June 2016 02:23] Report message to a moderator
|
|
|
Re: A Query for Sitting Arrangement [message #652860 is a reply to message #652857] |
Tue, 21 June 2016 02:44 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> select * from TAB_ARRANGEMENT order by floor;
FLOOR PERSONS_IN_ORDER
---------- --------------------------------------------------
1 KRISHNA,GOPAL,ARJUN,RAKHI,NISHA,,,,,,DEBA
2 SAKTHI,RAKESH,,,,,,,,
3 ,,,,,,,,,,
4 ARUN,PRADEEP,ABHI,,,,,,,,
5 RAGHAV,KARTIK,SAMULE,TINTIN,,,,,,
6 ,,,,,,,,,,
7 ,,,,,,,,,,
8 RAKESH.G,,,,,,,,,
9 GEETHA,PRIYA,NEHA,HEMA,,,,,,
10 PRASAD,,,,,,,,,
10 rows selected.
SQL> col seat_number format a11
SQL> col statement format a40
SQL> with
2 data as (
3 select floor, column_value,
4 '4B'||to_char(floor)||'W'||to_char(column_value) seat_number,
5 substr(PERSONS_IN_ORDER,
6 instr(','||PERSONS_IN_ORDER||',', ',', 1, column_value),
7 instr(','||PERSONS_IN_ORDER||',', ',', 1, column_value+1)
8 - instr(','||PERSONS_IN_ORDER||',', ',', 1, column_value)-1)
9 person
10 from TAB_ARRANGEMENT,
11 table(cast(multiset(select level from dual
12 connect by level <= regexp_count(PERSONS_IN_ORDER,',')+1)
13 as sys.odciNumberList))
14 )
15 select floor, seat_number,
16 'On floor '||to_char(floor)||' '||
17 nvl2(person,
18 initcap(person)||' sit''s at '||seat_number||'.',
19 seat_number||' is empty.')
20 statement
21 from data
22 order by floor, column_value
23 /
FLOOR SEAT_NUMBER STATEMENT
---------- ----------- ----------------------------------------
1 4B1W1 On floor 1 Krishna sit's at 4B1W1.
1 4B1W2 On floor 1 Gopal sit's at 4B1W2.
1 4B1W3 On floor 1 Arjun sit's at 4B1W3.
1 4B1W4 On floor 1 Rakhi sit's at 4B1W4.
1 4B1W5 On floor 1 Nisha sit's at 4B1W5.
1 4B1W6 On floor 1 4B1W6 is empty.
1 4B1W7 On floor 1 4B1W7 is empty.
1 4B1W8 On floor 1 4B1W8 is empty.
1 4B1W9 On floor 1 4B1W9 is empty.
1 4B1W10 On floor 1 4B1W10 is empty.
1 4B1W11 On floor 1 Deba sit's at 4B1W11.
2 4B2W1 On floor 2 Sakthi sit's at 4B2W1.
2 4B2W2 On floor 2 Rakesh sit's at 4B2W2.
2 4B2W3 On floor 2 4B2W3 is empty.
2 4B2W4 On floor 2 4B2W4 is empty.
2 4B2W5 On floor 2 4B2W5 is empty.
2 4B2W6 On floor 2 4B2W6 is empty.
2 4B2W7 On floor 2 4B2W7 is empty.
2 4B2W8 On floor 2 4B2W8 is empty.
2 4B2W9 On floor 2 4B2W9 is empty.
2 4B2W10 On floor 2 4B2W10 is empty.
3 4B3W1 On floor 3 4B3W1 is empty.
3 4B3W2 On floor 3 4B3W2 is empty.
3 4B3W3 On floor 3 4B3W3 is empty.
3 4B3W4 On floor 3 4B3W4 is empty.
3 4B3W5 On floor 3 4B3W5 is empty.
3 4B3W6 On floor 3 4B3W6 is empty.
3 4B3W7 On floor 3 4B3W7 is empty.
3 4B3W8 On floor 3 4B3W8 is empty.
3 4B3W9 On floor 3 4B3W9 is empty.
3 4B3W10 On floor 3 4B3W10 is empty.
3 4B3W11 On floor 3 4B3W11 is empty.
4 4B4W1 On floor 4 Arun sit's at 4B4W1.
4 4B4W2 On floor 4 Pradeep sit's at 4B4W2.
4 4B4W3 On floor 4 Abhi sit's at 4B4W3.
4 4B4W4 On floor 4 4B4W4 is empty.
4 4B4W5 On floor 4 4B4W5 is empty.
4 4B4W6 On floor 4 4B4W6 is empty.
4 4B4W7 On floor 4 4B4W7 is empty.
4 4B4W8 On floor 4 4B4W8 is empty.
4 4B4W9 On floor 4 4B4W9 is empty.
4 4B4W10 On floor 4 4B4W10 is empty.
4 4B4W11 On floor 4 4B4W11 is empty.
5 4B5W1 On floor 5 Raghav sit's at 4B5W1.
5 4B5W2 On floor 5 Kartik sit's at 4B5W2.
5 4B5W3 On floor 5 Samule sit's at 4B5W3.
5 4B5W4 On floor 5 Tintin sit's at 4B5W4.
5 4B5W5 On floor 5 4B5W5 is empty.
5 4B5W6 On floor 5 4B5W6 is empty.
5 4B5W7 On floor 5 4B5W7 is empty.
5 4B5W8 On floor 5 4B5W8 is empty.
5 4B5W9 On floor 5 4B5W9 is empty.
5 4B5W10 On floor 5 4B5W10 is empty.
6 4B6W1 On floor 6 4B6W1 is empty.
6 4B6W2 On floor 6 4B6W2 is empty.
6 4B6W3 On floor 6 4B6W3 is empty.
6 4B6W4 On floor 6 4B6W4 is empty.
6 4B6W5 On floor 6 4B6W5 is empty.
6 4B6W6 On floor 6 4B6W6 is empty.
6 4B6W7 On floor 6 4B6W7 is empty.
6 4B6W8 On floor 6 4B6W8 is empty.
6 4B6W9 On floor 6 4B6W9 is empty.
6 4B6W10 On floor 6 4B6W10 is empty.
6 4B6W11 On floor 6 4B6W11 is empty.
7 4B7W1 On floor 7 4B7W1 is empty.
7 4B7W2 On floor 7 4B7W2 is empty.
7 4B7W3 On floor 7 4B7W3 is empty.
7 4B7W4 On floor 7 4B7W4 is empty.
7 4B7W5 On floor 7 4B7W5 is empty.
7 4B7W6 On floor 7 4B7W6 is empty.
7 4B7W7 On floor 7 4B7W7 is empty.
7 4B7W8 On floor 7 4B7W8 is empty.
7 4B7W9 On floor 7 4B7W9 is empty.
7 4B7W10 On floor 7 4B7W10 is empty.
7 4B7W11 On floor 7 4B7W11 is empty.
8 4B8W1 On floor 8 Rakesh.G sit's at 4B8W1.
8 4B8W2 On floor 8 4B8W2 is empty.
8 4B8W3 On floor 8 4B8W3 is empty.
8 4B8W4 On floor 8 4B8W4 is empty.
8 4B8W5 On floor 8 4B8W5 is empty.
8 4B8W6 On floor 8 4B8W6 is empty.
8 4B8W7 On floor 8 4B8W7 is empty.
8 4B8W8 On floor 8 4B8W8 is empty.
8 4B8W9 On floor 8 4B8W9 is empty.
8 4B8W10 On floor 8 4B8W10 is empty.
9 4B9W1 On floor 9 Geetha sit's at 4B9W1.
9 4B9W2 On floor 9 Priya sit's at 4B9W2.
9 4B9W3 On floor 9 Neha sit's at 4B9W3.
9 4B9W4 On floor 9 Hema sit's at 4B9W4.
9 4B9W5 On floor 9 4B9W5 is empty.
9 4B9W6 On floor 9 4B9W6 is empty.
9 4B9W7 On floor 9 4B9W7 is empty.
9 4B9W8 On floor 9 4B9W8 is empty.
9 4B9W9 On floor 9 4B9W9 is empty.
9 4B9W10 On floor 9 4B9W10 is empty.
10 4B10W1 On floor 10 Prasad sit's at 4B10W1.
10 4B10W2 On floor 10 4B10W2 is empty.
10 4B10W3 On floor 10 4B10W3 is empty.
10 4B10W4 On floor 10 4B10W4 is empty.
10 4B10W5 On floor 10 4B10W5 is empty.
10 4B10W6 On floor 10 4B10W6 is empty.
10 4B10W7 On floor 10 4B10W7 is empty.
10 4B10W8 On floor 10 4B10W8 is empty.
10 4B10W9 On floor 10 4B10W9 is empty.
10 4B10W10 On floor 10 4B10W10 is empty.
105 rows selected.
[Edit: Add original table content]
[Updated on: Tue, 21 June 2016 04:50] Report message to a moderator
|
|
|
|
|
Re: A Query for Seating Arrangement [message #652895 is a reply to message #652866] |
Tue, 21 June 2016 21:11 |
|
Yes typo forgive for wrong spellings
and inspired by you all , rewritten
SELECT Floor_number,Seat_Number,
(CASE
WHEN person IS NOT NULL
THEN 'On Floor '||floor_number||' '||person||' sit''s at '||Seat_Number
ELSE 'On Floor '||floor_number||' '||Seat_Number||' is empty' END) STATEMENT
FROM
(SELECT floor_number, Person, PO, ('4B'||floor_number||'W'||po) Seat_Number
FROM
(WITH CTE AS (SELECT floor AS floor_number, REPLACE (PERSONS_IN_ORDER,',',' , ') AS Person FROM TAB_ARRANGEMENT)
SELECT TRIM(REGEXP_SUBSTR(Person, '[^,]+', 1, level)) AS Person,floor_number,level PO,DBMS_RANDOM.VALUE RV
FROM CTE CONNECT BY level <= REGEXP_COUNT(Person, '[^,]+')
AND PRIOR floor_number = floor_number
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL));
[Updated on: Tue, 21 June 2016 21:11] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:44:31 CDT 2024
|