How to add custom header to first column the sql server

Question

I am working on creating the pipeline in the azure data factory. I don't know how to transform rows into columns, but I have tried so far to query for an aggregate based on date. I need to variable endDate as column and row are to be endDate value. I think this is not optimal at all but I haven't been able to figure out a better way. I have a query like this,

@concat('Select s.StoreNumber,
              tt.TransactionType,
              tt.IsReturn,
              Sum(Convert(Money, f.Amount)) As TotalSales,
              Sum(f.Qty) As TotalSalesQty
From   fact.PrdTransaction f
              Join dim.Calendar c
                     On f.DateID = c.DateID
              Join dim.Store s
                     On f.StoreID = s.StoreID
              Join dim.TransactionType tt
                     On f.TransactionTypeID = tt.TransactionTypeID
Where  c.Date Between ''', variables('StartDate'), '''And''', 
        variables('EndDate'), '''
Group By      s.StoreNumber,
                     tt.TransactionType,
                     tt.IsReturn
Order By      s.StoreNumber,
                     tt.TransactionType,
                     tt.IsReturn')

I have End date is variable , I need to add Enddate as a column name. If I run a query like this. I got an error.

@concat('Select s.StoreNumber,
          c.Date =variables(''EndDate'')  as EndDate,
          tt.TransactionType,
          tt.IsReturn,
          Sum(Convert(Money, f.Amount)) As TotalSales,
          Sum(f.Qty) As TotalSalesQty
From   fact.PrdTransaction f
          Join dim.Calendar c
                 On f.DateID = c.DateID
          Join dim.Store s
                 On f.StoreID = s.StoreID
          Join dim.TransactionType tt
                 On f.TransactionTypeID = tt.TransactionTypeID
Where  c.Date Between ''', variables('StartDate'), '''And''', 
    variables('EndDate'), '''
Group By      s.StoreNumber,
                 tt.TransactionType,
                 tt.IsReturn
Order By      s.StoreNumber,
                 tt.TransactionType,
                 tt.IsReturn')

I need output like this:

EndDate    StoreNumber  TransactionType  IsReturn  TotalSales TotalSalesQty
12/10/2021 1              something 1     something1  something1 something 1
12/10/2021 2             something 2     something2  something2   something 2

PratikLad · Answer

As you want to give heading to column with variable which you are providing to pipeline to achieve this In a Query you can give like this

Enddate = ''',variables('Enddate'),'''

Thanks @Luuk, As suggested by him ''',variables(''EndDate''),''' as EndDate will also work.

Desired output

How to add custom header to first column the sql server

1 Answers