Add new columns to exsiting table in view

Question

I have the following SQL that creates the table below:

SELECT c.cOLUMN1 , c.cOLUMN2 , COALESCE (sc.cOLUMN3, 'XXX') AS cOLUMN3
FROM tabel AS c 


COLUMN1  COLUMN2   COLUMN3 

   1        1        XXX     
   1        1        26785  
   1        1        23432   
   1        1        XXX

I want to add two new columns - if the value in column3 is equal too 'XXX' then the new in NEWCOLUMN_A should be '0' and NEWCOLUM_B should be '1'.

Otherwise the new in NEWCOLUMN_A should be '1' and NEWCOLUM_B should be '1'.

As shown below:

COLUMN1  COLUMN2   COLUMN3  NEWCOLUMN_A  NEWCOLUMN_B
   1        1        XXX       0            1  
   1        1        26785     1            1           
   1        1        23432     1            1 
   1        1        XXX       0            1

Visit http://stackoverflow.com/questions/4069611/how-can-i-dynamically-create-columns-in-sql-select-statement — Khurram Ali, Feb 07 '14 at 19:19

score 2 · Accepted Answer · answered Feb 07 '14 at 18:50

SELECT 
    c.cOLUMN1 , 
    c.cOLUMN2 , 
    COALESCE (c.cOLUMN3, 'XXX') AS cOLUMN3,
    (CASE WHEN COALESCE(c.cOLUMN3, 'XXX') = 'XXX' THEN 0 ELSE 1 END) AS NEWCOLUMN_A,
    1 AS NEWCOLUMN_B
FROM tabel AS c

Alternatively, since column 3 is derived...

SELECT 
    c.cOLUMN1 , 
    c.cOLUMN2 , 
    COALESCE (c.cOLUMN3, 'XXX') AS cOLUMN3,
    (CASE WHEN c.cOLUMN3 IS NULL THEN 0 ELSE 1 END) AS NEWCOLUMN_A,
    1 AS NEWCOLUMN_B
FROM tabel AS c

score 0 · Answer 2 · answered Feb 07 '14 at 18:50

0

First add the columns to the table:

ALTER TABLE tabel
    ADD NEWCOLUMN_A BIT NULL;
ALTER TABLE tabel
    ADD NEWCOLUMN_B BIT NULL;

then UPDATE the table:

UPDATE tabel
SET NEWCOLUMN_A = CASE WHEN COLUMN3 = 'XXX' THEN 0 ELSE 1 END,
    NEWCOLUMN_B = 1

answered Feb 07 '14 at 18:50

Mike Perrenoud

66,820
29
157
232

score 0 · Answer 3 · answered Feb 07 '14 at 18:51

I think this what you are looking to accomplish.

Declare @string As nvarchar(50)
Set @string = 'xxx'
Select CASE WHEN @string  = 'xxx' Then 0 Else 1 End as NEWCOLUM_A, 1 as NEWCOLUM_B
set @string = 'yyy'
Select CASE WHEN @string  = 'xxx' Then 0 Else 1 End as NEWCOLUM_A, 1 as NEWCOLUM_B

score 0 · Answer 4 · answered Feb 07 '14 at 18:52

0

SELECT c.cOLUMN1 
     , c.cOLUMN2 
     , COALESCE(c.cOLUMN3, 'XXX') AS cOLUMN3
     , CASE WHEN c.cOLUMN3 IS NULL THEN 1 ELSE 0 END AS NEWCOLUMN_A 
     , 1 AS NEWCOLUM_B 
FROM tabel AS c

answered Feb 07 '14 at 18:52

M.Ali

67,945
13
101
127

Add new columns to exsiting table in view

4 Answers4