Why FOR XML PATH() is used in this script?-CodePudding

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

CodePudding user response：

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://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

CodePudding user response：

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.