SQL Conversion to BAQ

[CSmith (Clint Smith)]

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).

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;

[bordway (Bruce Ordway)]

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

External Configurator

I am working on a tool for our sales people that automatically creates a sales approval drawing, calculates operation times, prices, etc. Its too complicated to be a configurator and besides its a web app so that people can use it in the field and not consume a licence. I need to be able to push/pull configurator information from the web app to Epicor. Eventually I would like to turn this into something that a customer could use to instantly get a quote. It is my understanding that this is not …

[CSmith (Clint Smith)]

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 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…