SQL: Change query output to have two separate columns from having rows with 2 values

user_14471602 0
Viewed 30

For some reason my query output is grouping together 2 columns into 1, and putting the 2 values in the same row like this:

PATIENT_NAME
--------------------------------------------------------
INSURANCE
-------------------------
Aimie Pepsodent
Manulife

Aka Fresh
Blue Cross

Apple Addaye
Blue Cross

But I want them to appear in two separate columns like my teacher's output:

PATIENT_NAME   INSURANCE
-------------- ----------------
Apple Addaye   Blue Cross
Roy Alflush    No Insurance
Shane Cane     No Insurance

Is there a way I can change it to this? Right now my sql query looks like this:

select (fname||' '||lname) patient_name,
       (nvl(l4_insurance_cos.company_name, 'No Insurance')) insurance
from l4_patients
left join l4_insurance_cos 
on l4_patients.ins_id = l4_insurance_cos.id
order by l4_patients.lname;
sqlplus oracle sql string
1 Answers

This is a pure SQLPlus display issue. The size of the line is too small for the two columns to fit in it, so SQLPlus splits the results on two lines.

You need to adjust the linesize of your terminal, and/or the display width of each column - by default, it corresponds to the maximum length of the resultset column (if you concatenate two columns in the query, that's the sum of the length of the two columns, with a limit of 4000 bytes for varchars).

The actual values will depend on your terminal and table definition, but here is an example:

set linesize 140                -- allow a total of 140 characters per line
column patient_name format a80  -- 80 characters for column "patient_name"
column insurance    format a60  -- 60 characters for column "insurance"

Then, you can run your query.

user_106767160
Related