0

I am trying to get log of share prices (matrix) in SQL Server. The column name are dynamic and have been by applying PIVOT

DECLARE 
    @cmd   AS NVARCHAR(MAX),
    @cols  AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)

SET @cols = (SELECT DISTINCT STRING_AGG(Cast(Quotename (Symbol)as NVARCHAR(MAX)),',') FROM #symbol)         
--print @cols

set @query = 'SELECT PriceDate,' + @cols + ' INTO ##temp3 from 
             (
                SELECT PriceDate,LastPrice,Symbol FROM #WORK
            ) x
            pivot 
            (
                sum(LastPrice)
                for Symbol in (' + @cols + ')
            ) p ORDER BY PriceDate'
    
execute(@query);

Now I have to apply log of price change on this column with dynamic column names.

1

And then I have to apply portfolio Covariance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Taha Ekram
  • 1
  • 1
  • Honestly, this type of pivoting is far better done in the application layer, *not* the SQL layer. You, however, don't explain what the problem is here; you say what you want to do but that is meaningless to us with no explanation of what that means. We don't know your system or data, not know what "price change" or "covariance" represents. – Thom A Dec 12 '21 at 10:45
  • I need to apply lag function on each column of this table for every date without fixing the names of the columns – Taha Ekram Dec 12 '21 at 10:51
  • Then that method of dynamic pivoting isn't going to work, I'm afraid. Like I mentioned, you would be *far* better off doing this in the application; "basic" dynamic pivoting isn't simple and doing something more complex is far from it. If you are using dynamic SQL you need to very much understand what it does, as it can be a very dangerous tool, and I'm not sure you do understand the above completely. For example you use the syntax `EXEC (@SQL)` which is seen as a bad habit, as it doesn't allow for parametrising your query. – Thom A Dec 12 '21 at 10:53
  • Sample data and expected results *as text* would help immensely – Charlieface Dec 12 '21 at 16:41

0 Answers0