Dynamic Pivot

by lokointhehouse2

JavaScript

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumnsMAAPSELECT AS NVARCHAR(MAX)
DECLARE   @PivotColumnsMAAP AS NVARCHAR(MAX)
DECLARE   @PivotColumnsLEASESELECT AS NVARCHAR(MAX)
DECLARE   @PivotColumnsLEASE AS NVARCHAR(MAX)



--max([dealerInvoicePrice]) as dealerInvoicePrice, 
-- max(market_code+'d')

SELECT  @PivotColumnsMAAPSELECT  = COALESCE(@PivotColumnsMAAPSELECT + ',','') + 'max (' + QUOTENAME(market_code) + ') as ' + QUOTENAME(+market_code+'_MAAP')
FROM (SELECT DISTINCT market_code FROM TBL_PRICINGTOOL_MODEL_PACKAGE_REDUCTION) AS PivotExample

SELECT   @PivotColumnsMAAP = COALESCE(@PivotColumnsMAAP + ',','') + QUOTENAME(market_code)  
FROM (SELECT DISTINCT market_code FROM TBL_PRICINGTOOL_MODEL_PACKAGE_REDUCTION) AS PivotExample

SELECT  @PivotColumnsLEASESELECT  = COALESCE(@PivotColumnsLEASESELECT + ',','') + 'max (' + QUOTENAME(market_code+'_L') + ') as ' + QUOTENAME(+market_code+'_LEASE')
FROM (SELECT DISTINCT market_code FROM TBL_PRICINGTOOL_MODEL_PACKAGE_REDUCTION) AS PivotExample

SELECT   @PivotColumnsLEASE = COALESCE(@PivotColumnsLEASE + ',','') + QUOTENAME(market_code+'_L')  
FROM (SELECT DISTINCT market_code FROM TBL_PRICINGTOOL_MODEL_PACKAGE_REDUCTION) AS PivotExample

SELECT   @PivotColumnsMAAPSELECT
SELECT   @PivotColumnsMAAP

SELECT @PivotColumnsLEASESELECT
SELECT @PivotColumnsLEASE


SET @SQLQuery = N'

DECLARE   @startdate AS NVARCHAR(MAX)
DECLARE   @enddate AS NVARCHAR(MAX)

SET   @startdate = ''06/01/2017 00:00:00''
SET   @enddate = ''06/30/2017 00:00:00''

select 
modelName, 
modelYear, 
axleDescription, 
trimDescription,
max([dealerInvoicePrice]) as dealerInvoicePrice, 
max([DD]) as dd, 
max([applicableConsumerIncentive]) as applicableConsumerIncentive, 
max([nationalMAAP]) as nationalMAAP, 
' + @PivotColumnsMAAPSELECT + ',
max([nationalLeaseLowest]) as nationalLeaseLowest, 
' + @PivotColumnsLEASESELECT + ',
max([zeroToDrive]) as zeroToDrive 
					
			from
			(
			SELECT
			m.modelName, 	
			MY.modelYear, 
			a.axleDescription,...