-1

Desired output-

Emp_name|Hobbies|age|DOB
______________________________
LOPEZ        |Football , Swimming , Fishing |19| 1999-05-11

Here in the question, the Hobbies column is having multiple records with comma separate, BUT I want in a SINGLE line (Vertically). All the records for hobbies should be a single record, like multiple values in single record. And, last display in one row.

Please help me creating a table and way to insert and fetch the record in postgres DB.

Subhashis Dey
  • 13
  • 5
  • I don't recommend a comma separated list (nor an array (Hint!)). Have a persons table, a hobbies table and another table that links the hobbies to a person. Also the age shouldn't be stored in the persons table, as it is obviously dependent on the birthday an the current time. But apart from that: What have you got so far and what is the exact problem with what you have so far. Please edit your question accordingly. – sticky bit May 26 '18 at 00:54
  • So can you help me creating a table like this and fetch the record in the similar manner which I explained. All the HOBBIES values should be together. My is desired result is not that complex. Hobbies have multiple values. How to insert and fetch it together. – Subhashis Dey May 26 '18 at 04:20

1 Answers1

0

So we want to reformat your 'hobbies' string to an array. You can use the ARRAY_AGG() function:

    CREATE TABLE new_hobbies AS
    SELECT
        name 
        , age 
        , DOB 
        , ARRAY_AGG(hobbies) AS hobbies
    FROM table
    GROUP BY
        name
        , age
        , dob

But yeah I agree with sticky bit's answer that normalisation of this single table would be a good idea. As well as not having an age value - to avoid issues with updates.

Rupert
  • 150
  • 1
  • 6
  • So can you help me creating a table like this and fetch the record in the similar manner which I explained. All the HOBBIES values should be together. My is desired result is not that complex. Hobbies have multiple values. How to insert and fetch it together. – Subhashis Dey May 26 '18 at 04:21
  • Can you help me inserting some values in the new_hobbies table? How shall i insert values in the array column? – Subhashis Dey May 28 '18 at 06:15
  • Thanks a lot Rupert. I got the result as expected. I would be very helpful if you can help me represent the output as below :- ` name | age | dob | hobbies ------------------------------------------------------------------------+-----+------------+---------------------------- Rocky | 27 | 1991-04-17 | Fishing | | | | Cricket | | | | Football |` – Subhashis Dey May 28 '18 at 08:24
  • I mean to say that the values should represent in vertical wise rather than comma separated. Can you help me in this? @Rupert – Subhashis Dey May 28 '18 at 08:38