CODEWITHSUNDEEP
sqlsql-server
Jasmine
Jasmine

Reputation: 391

SQL Add a column to dynamically get value from another column

I have a table like this:

LV1 | LV2 | LV3 
A   | B   |C
X   |Y    |Null

I want to add a column to get the bottom level,

LV1 | LV2 | LV3 |Bottom
A   | B   |C    |C
X   |Y    |Null |Y

How should I do that? - I am using SQL Server

Thanks

Upvotes: 2

Views: 62

Answers (3)

Chanukya
Chanukya

Reputation: 5893

 CREATE TABLE #table(LV1 VARCHAR(10) ,LV2 VARCHAR(10) , LV3 VARCHAR(10))
 INSERT INTO #table(LV1  ,LV2  , LV3)
 SELECT 'A','B','C' UNION ALL
 SELECT 'X','Y',null
 SELECT LV1  ,LV2  , LV3 , COALESCE(LV3,LV2,LV1) Bottom
 FROM #table

output

LV1 LV2 LV3 Bottom
A   B   C       C
X   Y   NULL    Y

Upvotes: 3

Mansoor
Mansoor

Reputation: 4192

use CTE to get your result :

 CREATE TABLE #table(LV1 VARCHAR(10) ,LV2 VARCHAR(10) , LV3 VARCHAR(10))
 INSERT INTO #table(LV1  ,LV2  , LV3)
 SELECT 'A','B','C' UNION ALL
 SELECT 'X','Y',null

 ;WITH CTE (LV1  ,LV2  , LV3 , Bottom) AS
 (
   SELECT LV1  ,LV2  , LV3 , COALESCE(LV3,LV2,LV1)
   FROM #table
 ) 

 SELECT * FROM CTE

Upvotes: 2

Buda Gavril
Buda Gavril

Reputation: 21657

You should create a function and then call this function for the default value:

alter table yourTable
add constraint 
df_Bottom_Default
default([dbo].[your_udf_function]())
for Bottom

Upvotes: 0

Related Questions