SQL Conversion to BAQ

, ,

I wonder if anyone could give me a BAQ equivalent for the following SQL? Or a suggested avenue for this to build out some reports. This Converges 21222 rows into 131 rows (easily usable in spreadsheet too). :scream:

Dynamic SQL for PcInputValues
DECLARE @SQL NVARCHAR(max) = NULL;

SET @SQL = CONCAT('SELECT [Company],[GroupSeq],[RelatedToTableName],'
, (SELECT DISTINCT STUFF( (
	SELECT DISTINCT ', ' + tt1.configFieldList
	FROM 	
		(SELECT DISTINCT CONCAT(          
		'min(case when [InputName] = ''',
		[InputName],
		''' then [InputValue] end) AS ',
		[InputName] ) configFieldList        
		FROM [EpicorERPTest].[Erp].[PcInputValue] WHERE [Company] ='EZCUST') tt1
	FOR XML PATH('')) ,1,1,'') 
FROM  
		(SELECT DISTINCT CONCAT(          
		'min(case when [InputName] = ''',
		[InputName],
		''' then [InputValue] end) AS ',
		[InputName] ) configFieldList        
		FROM [EpicorERPTest].[Erp].[PcInputValue] WHERE [Company] ='EZCUST') tt1 
),' FROM [EpicorERPTest].[Erp].[PcInputValue] WHERE [Company] =''EZCUST'' GROUP BY [Company],[GroupSeq],[RelatedToTableName]' );

EXECUTE sp_executesql @SQL;

Sorry, no answer

However, I was curious about the table name PcInputValues.
I found the thread below that included some BAQ related details I found interesting

Yes, I remember reading that post before, but could not remember the content as such I now know that my query cannot be turned into a BAQ :sob: so thank you for that @bordway! Looks like I will need a Service Connect or something to pull the data above for finance reporting purposes. I would build out the data using the configurator choices. As there are about 150 ish configurator inputs the query above basically turns 20K lines of individual inputs into about 130 in pivot table type format relating to orders/quotes/jobs… :scream: