CODEWITHSUNDEEP
sqloracle-database
Joshua Plyler
Joshua Plyler

Reputation: 11

Concatenation issues

I have a table "Patient" which has 5 fields.

CREATE TABLE PATIENT
(PAT_ID         CHAR (4) PRIMARY KEY,
 PAT_NAME       VARCHAR (7),
 ADMITTED       DATE,
 ROOM           CHAR (3),
 DOCTOR         VARCHAR (7));

INSERT INTO PATIENT VALUES
(1001, 'FRED', '30-MAR-07', 101, 'PAYNE');

I want to create a view where my output to look like this:

Patient      Doctor       Room         Admitted    
"-----------------------------------------------"
1001 Fred    Payne        101      March 31, 2007

The problem is that I want to concatenate Pat_name and Pat_ID into their own separate "Patient" column but ALSO have the other three fields also concatenated with it (notice there is no separation in the 'dashes').

Basically, I want one single column with all 5 columns concatenated, but I need to also have the first two columns combined into a single subcolumn named "patient".

Can you concatenate within a concatenation?

Upvotes: 0

Views: 699

Answers (3)

RustamIS
RustamIS

Reputation: 697

here is the query that creates view by name "view_name":

  create or replace view view_name as
select t.Pat_Id || ' ' || t.Pat_Name Patient,
       t.Doctor,
       t.Room,
       to_char(t.Admitted, 'Month dd, yyyy') Admitted
  from Patient t 
with read only;

you can select as

SELECT * FROM VIEW_NAME

I think that here provided enough information, that you can add or manipulate (other) columns wether to show.

Upvotes: 0

rutter
rutter

Reputation: 11452

It turns out you're actually using Oracle Database. I'm still not sure exactly which fields you're trying to concatenate in which way, but this should be enough to get you rolling:

CREATE VIEW PATIENT_REPORT 
AS
SELECT
  PATIENT.PAT_ID||' '||PATIENT.PAT_NAME as "patient",
  PATIENT.DOCTOR||' '||PATIENT.ROOM||' '||PATIENT.ADMITTED as "details"
FROM PATIENT;

Here's a live demo on SQL Fiddle.

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191819

SELECT CONCAT(PAT_ID, ' ', PAT_NAME) AS Patient,
    DOCTOR AS Doctor, ROOM AS Room, ADMITTED AS Admitted
FROM PATIENT

Upvotes: 1

Related Questions