How to SELECT multi-columns from a column? I have a table with the structure

book_id int(11),
section_id int(11),
note_id int(11),
item_number int(11),
text varchar(255),
UNIQUE(book_id,section_id,note_id,item_number)


item_number can be 1 or 2 only.

How can I make a SELECT query to get text for item_number 1 and 2 together for each book_id,section_id,note_id

SELECT book_id, select_id, note_id, TEXT_number_1, TEXT_number_2

TEXT_number_1 = text (WHERE item_number=1)
TEXT_number_2 = text (WHERE item_number=2)


I considered two options:


GROUP BY, which can give me a CONCAT(TEXT_number_1,TEXT_number_2)
JOIN, which I am not sure what is the best query.
SELECT t0.book_id, t0.select_id, t0.note_id, t1.TEXT_number_1, t2.TEXT_number_2
FROM (SELECT DISTINCT book_id, select_id, note_id
      FROM `table`) t0
LEFT JOIN `table` t1 ON t0.book_id=t1.book_id
                     AND t1.item_number=1   
                  /* AND t0.select_id=t1.select_id 
                     AND t0.note_id=t1.note_id */
LEFT JOIN `table` t2 ON t0.book_id=t2.book_id 
                     AND t2.item_number=2   
                  /* AND t0.select_id=t2.select_id 
                     AND t0.note_id=t2.note_id */