sql apache derby separate/ combine duplicate? (pivot query)

Question

I have a table like this

How can I do that?

Answer 1

Apache Derby does not have a "pivot" feature that I know of, but it does provide row_number() which can be used to arrange hobbies into columns using standard aggregation functions containing case expressions (known as "conditional aggregates")

SELECT
      name
    , MAX(CASE WHEN rn = 1 THEN hobby END) Hobby1
    , MAX(CASE WHEN rn = 2 THEN hobby END) Hobby2
    , MAX(CASE WHEN rn = 3 THEN hobby END) Hobby3
    , MAX(CASE WHEN rn = 4 THEN hobby END) Hobby4
    , MAX(CASE WHEN rn = 5 THEN hobby END) Hobby5
FROM (
      SELECT
            name
          , hobby
          , row_number () OVER (PARTITION BY NAME ORDER BY hobby) AS rn
      FROM T
      ) d
WHERE rn <= 5
GROUP BY
      d.name
;