Hi!!
First of all sorry for the long thread but i really don't know how to explain otherwise. Please let me know if my question is not clear enough. I hope that this is possible.
I have the follwing query:
SELECT MAX(CASE WHEN t.plate_column=1 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c1,
MAX(CASE WHEN t.plate_column=2 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c2,
MAX(CASE WHEN t.plate_column=3 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c3,
MAX(CASE WHEN t.plate_column=4 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c4,
MAX(CASE WHEN t.plate_column=5 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c5,
MAX(CASE WHEN t.plate_column=6 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c6,
MAX(CASE WHEN t.plate_column=7 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c7,
MAX(CASE WHEN t.plate_column=8 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c8,
MAX(CASE WHEN t.plate_column=9 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c9,
MAX(CASE WHEN t.plate_column=10 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c10,
MAX(CASE WHEN t.plate_column=11 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c11,
MAX(CASE WHEN t.plate_column=12 THEN t.u_box_code || '-' || t.u_box_position ELSE null END) c12
FROM ( SELECT plate.plate_id,
aliquot.plate_column,
aliquot.plate_row,
sample_user.u_box_code,
sample_user.u_box_position,
sample_user.u_box_column,
sample_user.u_box_row
FROM lims_sys.plate, lims_sys.aliquot,lims_sys.sample,lims_sys.sample_user
WHERE plate.plate_id = aliquot.plate_id
AND aliquot.sample_id = sample.sample_id
AND sample.sample_id = sample_user.sample_id
AND plate.plate_id IN (874)
) t
GROUP BY t.plate_id,t.plate_row
ORDER BY t.plate_id,t.plate_row
[pre/]
The results of this query are:
[pre]
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
F515-C07 F515-C08 F515-C09 F515-C10 F515-G05 F515-G06 F515-G07 F515-G08 F515-G09 F516-G10
F516-G11 F516-G12 F516-H01 F516-H02 F516-H03 F516-H04 F516-H05 F516-H06 F516-H07 N451-C04 N451-C05 N451-C06
N451-C07 N451-C08 N451-C09 N451-G02 N451-G03 N451-G04 N452-F11 N452-F12 N452-G01 N452-G02 N452-G03
N452-G04 N452-G05 N452-G06 N452-G07 N452-G08 N453-B05 N453-B06 N458-A11 N458-A12 N458-B03
N458-B04 N458-B05 N458-B06 N458-B07 N458-B08 N458-B09 N458-B10 N458-B11 N458-B12 N458-C01 N458-C02 N458-C03
N458-C04 N458-C05 N458-C06 N458-C07 N458-C08 N458-C09 N458-C10 N458-C11 N458-C12 N458-D01 N458-D02 N458-D03
N458-D04 N458-D05 N458-D06 N458-D07 N458-D08 N458-D09 N458-D10 N458-D11 N458-D12 N458-E03
N458-E04 N458-E05 N458-E06 N458-E07 N458-E08 N458-E09 N458-E10 N458-E11 N458-E12 N458-F01 N458-F02 N458-F03
[pre/]
What I actually want is that for each new u_box_code a status (C1status, C2status etc.) for every value change from 0 to 1 to 0 to 1 etc.Then the wanted results looks like:
[pre]
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C1status C2status C3status C4status C5status C6status C7status C8status C9status C10status C11status C12status
F515-C07 F515-C08 F515-C09 F515-C10 F515-G05 F515-G06 F515-G07 F515-G08 F515-G09 F516-G10 0 0 0 0 0 0 0 0 0 1
F516-G11 F516-G12 F516-H01 F516-H02 F516-H03 F516-H04 F516-H05 F516-H06 F516-H07 N451-C04 N451-C05 N451-C06 1 1 1 1 1 1 1 1 1 0 0 0
N451-C07 N451-C08 N451-C09 N451-G02 N451-G03 N451-G04 N452-F11 N452-F12 N452-G01 N452-G02 N452-G03 0 0 0 0 0 0 1 1 1 1 1
N452-G04 N452-G05 N452-G06 N452-G07 N452-G08 N453-B05 N453-B06 N458-A11 N458-A12 N458-B03 1 1 1 1 1 0 0 1 1 1
N458-B04 N458-B05 N458-B06 N458-B07 N458-B08 N458-B09 N458-B10 N458-B11 N458-B12 N458-C01 N458-C02 N458-C03 1 1 1 1 1 1 1 1 1 1 1 1
N458-C04 N458-C05 N458-C06 N458-C07 N458-C08 N458-C09 N458-C10 N458-C11 N458-C12 N458-D01 N458-D02 N458-D03 1 1 1 1 1 1 1 1 1 1 1 1
N458-D04 N458-D05 N458-D06 N458-D07 N458-D08 N458-D09 N458-D10 N458-D11 N458-D12 N458-E03 1 1 1 1 1 1 1 1 1 1
N458-E04 N458-E05 N458-E06 N458-E07 N458-E08 N458-E09 N458-E10 N458-E11 N458-E12 N458-F01 N458-F02 N458-F03 1 1 1 1 1 1 1 1 1 1 1 1
[pre/]
The most difficult part is the combination of the wanted results from thread 401195 and this.
Conclusion:
The final results must be :
[pre]
C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12 C1status C2status C3status C4status C5status C6status C7status C8status C9status C10status C11status C12status
C07-F515 C10-F515 G05-F515 G09-F515 G10-F516 0 0 0 0 0 0 0 0 0 1
G11-F516 G12-F516 H01-F516 H07-F516 C04-N451 C06-N451 1 1 1 1 1 1 1 1 1 0 0 0
C07-N451 C09-N451 G02-N451 G03-N451 G04-N451 F11-N452 F12-N452 G01-N452 G02-N452 G03-N452 0 0 0 0 0 0 1 1 1 1 1
G04-N452 G08-N452 B05-N453 B06-N453 A11-N458 A12-N458 B03-N458 1 1 1 1 1 0 0 1 1 1
B04-N458 B12-N458 C01-N458 C03-N458 1 1 1 1 1 1 1 1 1 1 1 1
C04-N458 C12-N458 D01-N458 D03-N458 1 1 1 1 1 1 1 1 1 1 1 1
D04-N458 D12-N458 E03-N458 1 1 1 1 1 1 1 1 1 1
E04-N458 E12-N458 F01-N458 F03-N458 1 1 1 1 1 1 1 1 1 1 1 1
[pre/]
The query of this without the statusses is:
[pre]
SELECT c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,plate_id,plate_row
FROM ( SELECT c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12, rownum r1,plate_id,plate_row
FROM ( SELECT MAX(CASE WHEN t.plate_column=1 THEN t.u_box_position|| '-' || t.u_box_code ELSE null END) c1,
MAX(CASE WHEN t.plate_column=2 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c2,
MAX(CASE WHEN t.plate_column=3 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c3,
MAX(CASE WHEN t.plate_column=4 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c4,
MAX(CASE WHEN t.plate_column=5 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c5,
MAX(CASE WHEN t.plate_column=6 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c6,
MAX(CASE WHEN t.plate_column=7 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c7,
MAX(CASE WHEN t.plate_column=8 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c8,
MAX(CASE WHEN t.plate_column=9 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c9,
MAX(CASE WHEN t.plate_column=10 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c10,
MAX(CASE WHEN t.plate_column=11 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c11,
MAX(CASE WHEN t.plate_column=12 THEN t.u_box_position || '-' || t.u_box_code ELSE null END) c12,
t.plate_id,
t.plate_row
FROM ( SELECT plate.plate_id,
aliquot.plate_column,
aliquot.plate_row,
sample_user.u_box_code,
sample_user.u_box_position,
sample_user.u_box_column,
sample_user.u_box_row
FROM lims_sys.plate, lims_sys.aliquot,lims_sys.sample,lims_sys.sample_user
WHERE plate.plate_id = aliquot.plate_id
AND aliquot.sample_id = sample.sample_id
AND sample.sample_id = sample_user.sample_id
AND plate.plate_id IN (874)
) t,
( SELECT min(u_box_column) min_col ,max(u_box_column) max_col,u_box_row,u_box_code,plate_row
FROM ( SELECT plate.plate_id,
aliquot.plate_column,
aliquot.plate_row,
sample_user.u_box_code,
sample_user.u_box_position,
sample_user.u_box_column,
sample_user.u_box_row
FROM lims_sys.plate, lims_sys.aliquot,lims_sys.sample,lims_sys.sample_user
WHERE plate.plate_id = aliquot.plate_id
AND aliquot.sample_id = sample.sample_id
AND sample.sample_id = sample_user.sample_id
AND plate.plate_id IN (874))
GROUP BY u_box_row,u_box_code,plate_row
) s
WHERE t.u_box_column in (min_col,max_col)
AND t.u_box_row = s.u_box_row
AND t.plate_row = s.plate_row
GROUP BY t.plate_id,t.plate_row
ORDER BY t.plate_id,t.plate_row)
) a,
( SELECT rownum r2
FROM all_objects
WHERE rownum <9
) b
WHERE a.r1(+) = b.r2
[pre/]