All Forums Database
Tnewbee 215 posts Joined 05/10
29 Jul 2010
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!!

Tnewbee 215 posts Joined 05/10
29 Jul 2010

Dnoeth, can you please help ?

Jimm 298 posts Joined 09/07
29 Jul 2010

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. );

Tnewbee 215 posts Joined 05/10
30 Jul 2010

Thank you! :)

irfanali 5 posts Joined 08/10
04 Aug 2010

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

Irfan Ali

You must sign in to leave a comment.