Add a column in a create table as(sel...) statement
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.