Add a column in a create table as(sel...) statement

Database
Enthusiast

Add a column in a create table as(sel...) statement

Hi!

I am trying to create a table as below:
create table temp as
(SELECT A.*, 'Cust NAME', C.FRST_NM, C.LST_NM
FROM table A
INNER JOIN table B
ON A.ID = B.ID
INNER JOIN table c
ON B.P_ID = C.P_ID. );

But I want my output to look like this:
Cust id Cust typ Cust no Field_nm field_txt.
100 EVT 23 Cust NAME JOHN SMITH, ADAM SMITH & BBC COMPANY
100 EVT 13 Cust NAME APRIL LI ,BBC COMPANY

So basically I want the column name to be field_nm and the value inside it to be cust name. How to achieve this in the same CReate table statement without having to use an ALTER statemt after it. I have multiple such queries which I will be submitting through SAS, so I do not want to alter the table everytime it it created.

Thank you!!
4 REPLIES
Enthusiast

Re: Add a column in a create table as(sel...) statement

Dnoeth, can you please help ?
Enthusiast

Re: Add a column in a create table as(sel...) statement

Something like:

create table temp as
(SELECT A.*, 'Cust NAME' AS Field_Nm,
Trim(C.FRST_NM)||' '||Trim(C.LST_NM) As Field_Txt
FROM A
INNER JOIN B
ON A.ID = B.ID
INNER JOIN c
ON B.P_ID = C.P_ID. );
Enthusiast

Re: Add a column in a create table as(sel...) statement

Thank you! :)
Enthusiast

Re: Add a column in a create table as(sel...) statement

Tnewbee,
Your SQL is not executing with me unless you specify WITH DATA or WITH NO DATA at the end of sql
Regards