You can use GROUP_CONCAT
:
SELECT person_id,
GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Ludwig stated in his comment, you can add the DISTINCT
operator to avoid duplicates:
SELECT person_id,
GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY
:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
SET group_concat_max_len = 2048;
Of course, you can change 2048
according to your needs. To calculate and assign the value:
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);
It seems you have some sort of resource leak. Do you close connections properly ?
Also, as ar said in comment, why don't you just keep this connection open ? IIRC Establishing conn is costly operation in any DBMS.
Also, from documentation:
ORA-12560 -- TNS:protocol adapter error
Cause: A generic protocol adapter error occurred.
Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and reexecute the operation. Turn off tracing when the operation is complete.
UPDATE:
Problem could be caused by overflow of Windows event journal. Check Oracle's events here:
Start menu => Control Panel => Administrative Tools => Event Viewer
You should either clear journal manually or increase its' size
Best Answer
Go to Start -> Run -> Services.msc in Windows. Locate OracleService < SID > (here
OracleServiceORCL
) and click onStart
to start the oracle database service (if not already running)Once it is up and running, from the command prompt run the following:
(tnsalias entry you can find it in
tnsnames.ora
file)