CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

Naming column based on value of a variable

I have a simple query shown below, which works fine.

However there are two columns with no names shown below. I would like to name the columns based on the values of the variables @date1 & @date2

isnull(t1.Quantity, 0), isnull(t2.Quantity,0)

So I would have something like this

isnull(t1.Quantity, 0) [18-Apr-17], isnull(t2.Quantity,0) [19-Apr-17]

Is this possible?

My Query

 declare @region nvarchar(10), @dateT1 date, @dateT2 date, @sd nvarchar(20)
 set @region= 'SF1611'
 set @dateT1 = '2017-04-18'
 set @dateT2 = '2017-04-19'
 set @sd = convert(nvarchar(20), @dateT1, 7)

;with t1 as
(
  select Id, Quantity  
  from myTable
  where Region = @region and dateQ = @dateT1
), t2 as 
(
  select Id, Quantity
  from myTable
  where Region = @region and dateQ = @dateT2
)
select coalesce(t1.Id, t2.Id) Id, isnull(t1.Quantity, 0), isnull(t2.Quantity,0), isnull(t1.Quantity, 0) - isnull(t2.Quantity,0) diff
from t1 full outer join t2
on t1.Id  = t2.Id
where isnull(t1.Quantity, 0) - isnull(t2.Quantity,0) <> 0

Upvotes: 0

Views: 51

Answers (1)

LONG
LONG

Reputation: 4610

You could use dynamic SQL, try to print the result to see whether it is a valid query before you comment in the EXEC

 declare @regionnvarchar(10), @dateT1 date, @dateT2 date, @sd nvarchar(20)
 set @region= 'SF1611'
 set @dateT1 = '2017-04-18'
 set @dateT2 = '2017-04-19'
 set @sd = convert(nvarchar(20), @dateT1, 7)

;with t1 as
(
  select Id, Quantity  
  from myTable
  where Region = @region and dateQ = @dateT1
), t2 as 
(
  select Id, Quantity
  from myTable
  where Region = @region and dateQ = @dateT2
)


declare @SQL VARCHAR(max)

SET @SQL = 

'select coalesce(t1.Id, t2.Id) Id, isnull(t1.Quantity, 0) as ' + @dateT1 +', isnull(t2.Quantity,0) as ' +@dateT2 + ' ,(isnull(t1.Quantity, 0)- isnull(t2.Quantity,0)) as diff
from t1 full outer join t2
on t1.Id  = t2.Id
where isnull(t1.Quantity, 0) - isnull(t2.Quantity,0) <> 0'

print (@SQL)
--EXEC(@SQL)

Upvotes: 2

Related Questions