0

I got a help from some one to compose below sql but full script is not written by me. so i have bit of confusion how the below sql is working ?

CREATE Proc USP_GetValuationValue            
(            
   @Ticker VARCHAR(10),            
   @ClientCode VARCHAR(10),            
   @GroupName VARCHAR(10)            
)            
AS     

DECLARE @SPID VARCHAR(MAX), --Is this even used now?  
        @SQL nvarchar(MAX),            
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);            

SELECT @SPID=CAST(@@SPID AS VARCHAR);      
                       
    SET @SQL = N'SELECT * FROM (SELECT  min(id) ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder,' + STUFF((SELECT N',' + @CRLF + N'       ' +            
                N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN FieldValue END) AS ' + QUOTENAME(FieldName)            
        FROM tblValuationSubGroup g              
        WHERE ticker=@Ticker AND ClientCode=@ClientCode AND GroupName=@GroupName            
        GROUP BY FieldName            
        ORDER BY MIN(FieldOrder)            
        FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +            
    N'FROM (select * from tblValuationFieldValue' + @CRLF +            
    N'WHERE Ticker = @Ticker AND ClientCode = @ClientCode AND GroupName= @GroupName) f' + @CRLF +            
    N'GROUP BY f.ticker,f.ClientCode,f.GroupName,f.RecOrder) X' + @CRLF +  
    N'ORDER BY Broker;';             
                     
PRINT @SQL;

Below sql is generated after executing above dynamic sql

SELECT * FROM (SELECT  min(id) ID,f.ticker,f.ClientCode,f.GroupName,f.RecOrder,
       MAX(CASE FieldName WHEN 'Last Update' THEN FieldValue END) AS [Last Update],
       MAX(CASE FieldName WHEN 'Broker' THEN FieldValue END) AS [Broker],
       MAX(CASE FieldName WHEN 'Rating' THEN FieldValue END) AS [Rating],
       MAX(CASE FieldName WHEN 'Equivalent Rating' THEN FieldValue END) AS [Equivalent Rating],
       MAX(CASE FieldName WHEN 'Target Price' THEN FieldValue END) AS [Target Price]
FROM (select * from tblValuationFieldValue
WHERE Ticker = @Ticker AND ClientCode = @ClientCode AND GroupName= @GroupName) f
GROUP BY f.ticker,f.ClientCode,f.GroupName,f.RecOrder) X
ORDER BY Broker;

This part is not clear why used in above sql? FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'')

why FOR XML PATH() has been used here ? i always use FOR XML PATH() to generate xml with data from table.

please help me to understand first dynamic sql like how it is working.

Thanks

Thom A
  • 88,727
  • 11
  • 45
  • 75
Ramesh Dutta
  • 99
  • 8
  • 3
    This is a well-known trick to perform string concatenation in versions of SQL Server prior to 2017, where `STRING_AGG` was not available. – Jeroen Mostert Jun 13 '22 at 18:39
  • 1
    In your particular case it creates a comma separated list of the `FieldName` column values. – Yitzhak Khabinsky Jun 13 '22 at 18:42
  • As I can't see a question I've answered to you where I give you this code (and I am *very* confident it is *mine* due to the syntax throughout the query) did you speak to whomever gave you the code, as hopefully they understand it. If you did get it from me, you could just comment under the answer that I gave you the solution on. If they don't understand it, the code should be properly cited to link to where I gave the answer, so you can comment there too. – Thom A Jun 13 '22 at 18:42
  • 1
    [Related answer](https://dba.stackexchange.com/questions/207371/please-explain-what-does-for-xml-path-type-value-nvarcharmax/207380#207380). – Dan Guzman Jun 13 '22 at 18:49

2 Answers2

2

FOR XML PATH('') is used in older versions of SQL server (pre 2017) to get values from multiple rows into one. Since you're setting a variable, @SQL, you're setting a single string and therefore need one row returned.

FOR XML PATH('') produces all the MAX(CASE FieldName [...] with data from the tblValuationSubGroup table. Each row in tblValuationSubGroup contains one field. FOR XML PATH('') adds the rows into one string, which is later turned into fields during execution.

The issue FOR XML PATH('') solves in older versions of SQL Server has been solved with the STRING_AGG() function since SQL Server 2017. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

Erik Blomgren
  • 866
  • 4
  • 8
  • Hi, can you please modify & re-post above code where you will use `STRING_AGG()` instead of `FOR XML PATH('')` . it is a small request. – Ramesh Dutta Jun 13 '22 at 18:57
  • i have seen that `FOR XML PATH('')` is used to display row's values in single row. here is one url https://stackoverflow.com/a/18235816/18531882 but there stuff() is used but in my case stuff not used. `Type` is any field name ? what is the meaning of this line `.value('(./text())[1]','nvarchar(MAX)'),1,10,N'')` ? please help me to understand. thanks – Ramesh Dutta Jun 13 '22 at 19:10
  • I'm not all that familiar with FOR XML PATH('') to be honest. However, Type is not a field but rather a part of the FOR XML PATH('') function used to help define the output data type. .value is to retrieve the value instead of xml code, from what I understand.. – Erik Blomgren Jun 13 '22 at 20:32
-1

FOR XML PATH is being to dynamically generate the last 5 columns in your query (the MAX(CASE FieldName WHEN '...' THEN FieldValue END) AS [...] columns) using the FieldName column of tblValuationSubGroup.

Deirdre O'Leary
  • 420
  • 2
  • 6