CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
Juventus tech
Juventus tech

Reputation: 95

How to add a column on fly ?

I am facing different kind of problem. In select query I want to add a temporary column on fly based on other columns value.

I have 2 columns

IsOpeningClosingDateToo (tinyint),
HearingDate Date

Now I want to check that if IsOpeningClosingDate = 1 then 

Select HearingDate, HearingDate as 'OpeningDate'

If IsOpeningClosingDate= 2

Select HearingDate, HearingDate as 'ClosingDate'

I have tried to do this but failed:

SELECT 
      ,[HearingDate]
      ,CASE [IsOpeningClosingDate]
       when 1 then [HearingDate] as OpeningDate
       When 0 then [HearingDate] as ClosingDate
       end as 'test'
   ]
  FROM [LitMS_MCP].[dbo].[CaseHearings]

Upvotes: 1

Views: 668

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would suggest returning three columns. Then you can fetch the values in on the application side:

SELECT HearingDate,
       (CASE WHEN IsOpeningClosingDate = 1 THEN HearingDate END) as OpeningDate,
       (CASE WHEN IsOpeningClosingDate = 0 THEN HearingDate END) as ClosingDate
 FROM [LitMS_MCP].[dbo].[CaseHearings];

Alternatively, you could just fetch HearingDate and IsOpeningClosingDate and do the comparison in Python.

The important point is that the columns in a SQL query are fixed by the SELECT. You cannot vary the names or types of the columns conditionally within the query.

Upvotes: 3

Related Questions