There is a table of photos (photo_id, exam_id, photo) .

photo_id is the primary key. exam_id - foreign key, photo - binary data (in PostgreSQL, this is bytea )

I want to display the tablet using only SQL tools (exam_id, photo1, photo2, photo3) .

Made this sample: (exam_id, photo_id_1, photo_id_2, photo_id_3)

SELECT photos.exam_id, last(CASE WHEN num=1 THEN photos.exam_photo ELSE null END) id_1, max(CASE WHEN num=2 THEN photos.exam_photo_id ELSE null END) id_2, max(CASE WHEN num=3 THEN photos.exam_photo_id ELSE null END) id_3 FROM (SELECT exam_id, row_number() over(partition BY exam_id ORDER BY exam_photo_id) num, exam_photo_id, exam_photo FROM examsphotos ORDER BY exam_id) photos GROUP BY photos.exam_id;

How to remake this script so that the sample was binary data, I can not think of. The forehead does not work, for bytea there are no suitable aggregating functions.

  • Take three copies of the table and combine the equality of exam_id and excess photo_id. I dont know. in which SQL dialect it will NOT work ... - Akina

1 answer 1

Cast the type to text, from which you can take max () and, after grouping, bring the type back to bytea:

 select exam_id, max(CASE WHEN num=1 THEN photo END)::bytea p1, max(CASE WHEN num=2 THEN photo END)::bytea p2, max(CASE WHEN num=3 THEN photo END)::bytea p3 from ( select exam_id,row_number() over(partition by exam_id order by NULL) num, photo::text from examsphotos ) X group by exam_id

Either refuse to use aggregate functions in favor of the window function lead() , which returns data from the following lines. But in this case, the number of rows in the sample will be equal to the number of rows in the table and you will need to leave only the first ones, in which lead() collects all the necessary data.

 select * from ( select exam_id, photo p1, lead(photo,1) over(partition by exam_id order by exam_photo_id) p2, lead(photo,2) over(partition by exam_id order by exam_photo_id) p3, row_number() over(partition by exam_id order by exam_photo_id) rn from examsphotos ) A where rn=1
  • Thank you for the detailed answer - 0xd9