
-- From Reports page set as Unpivot AccountsMap, NetSalesMap, MarginMap into singel column
	SELECT RepKey, RepLineDesc, MappingKey
	INTO #ReportMap
	FROM 
	(
		SELECT RepKey, RepLineDesc, 
		SMap01,SMap02,SMap03,SMap04,SMap05,SMap06,SMap07,SMap08,SMap09,SMap10
		FROM pbi.dim_MA_Reports
	) As tbl
	UNPIVOT
	(
	MappingKey FOR Subject IN(SMap01,SMap02,SMap03,SMap04,SMap05,SMap06,SMap07,SMap08,SMap09,SMap10)
	) As upvt
	ORDER BY RepKey

--Unpivot ignores NULLS so append NULLS and Make mappingkey = repkey
	INSERT INTO #ReportMap
	SELECT r.RepKey, r.RepLineDesc, r.RepKey as MappingKey 
	FROM pbi.dim_MA_Reports r
	LEFT JOIN #ReportMap m
	ON r.RepKey = m.RepKey
	WHERE m.RepKey is NULL


	SELECT RepKey, RepLineDesc, Map
	INTO #MappingTable
	FROM 
	(
		Select r.RepKey, r.RepLineDesc, r.RepKey as RepKeyToMap, r.MappingKey, 
		r01.MappingKey as Map01, r02.MappingKey as Map02, r03.MappingKey as Map03,
		r04.MappingKey as Map04, r05.MappingKey as Map05, r06.MappingKey as Map06,
		r07.MappingKey as Map07, r08.MappingKey as Map08, r09.MappingKey as Map09,
		r10.MappingKey as Map10, r11.MappingKey as Map11, r12.MappingKey as Map12,
		r13.MappingKey as Map13, r14.MappingKey as Map14, r15.MappingKey as Map15,
		r16.MappingKey as Map16, r17.MappingKey as Map17, r18.MappingKey as Map18,
		r19.MappingKey as Map19, r20.MappingKey as Map20
		From #ReportMap r
		LEFT JOIN #ReportMap r01
		on r01.repKey = r.MappingKey
		LEFT JOIN #ReportMap r02
		on r02.repKey = r01.MappingKey
		LEFT JOIN #ReportMap r03
		on r03.repKey = r02.MappingKey
		LEFT JOIN #ReportMap r04
		on r04.repKey = r03.MappingKey
		LEFT JOIN #ReportMap r05
		on r05.repKey = r04.MappingKey
		LEFT JOIN #ReportMap r06
		on r06.repKey = r05.MappingKey
		LEFT JOIN #ReportMap r07
		on r07.repKey = r06.MappingKey
		LEFT JOIN #ReportMap r08
		on r08.repKey = r07.MappingKey
		LEFT JOIN #ReportMap r09
		on r09.repKey = r08.MappingKey
		LEFT JOIN #ReportMap r10
		on r10.repKey = r09.MappingKey
		LEFT JOIN #ReportMap r11
		on r11.repKey = r10.MappingKey
		LEFT JOIN #ReportMap r12
		on r12.repKey = r11.MappingKey
		LEFT JOIN #ReportMap r13
		on r13.repKey = r12.MappingKey
		LEFT JOIN #ReportMap r14
		on r14.repKey = r13.MappingKey
		LEFT JOIN #ReportMap r15
		on r15.repKey = r14.MappingKey
		LEFT JOIN #ReportMap r16
		on r16.repKey = r15.MappingKey
		LEFT JOIN #ReportMap r17
		on r17.repKey = r16.MappingKey
		LEFT JOIN #ReportMap r18
		on r18.repKey = r17.MappingKey
		LEFT JOIN #ReportMap r19
		on r19.repKey = r18.MappingKey
		LEFT JOIN #ReportMap r20
		on r20.repKey = r19.MappingKey

	) As tbl
	UNPIVOT
	(
	Map FOR Subject
			IN(
				RepKeyToMap, MappingKey, Map01, Map02,Map03,Map04,Map05,Map06,Map07,Map08
				, Map09, Map10,Map11,Map12,Map13,Map14,Map15,Map16,Map17,Map18,Map19,Map20
				)
	) As upvt
	ORDER BY RepKey

--Query below useful to check elements of mapping
--SELECT DISTINCT m.RepKey, m.RepLineDesc,m.Map, e.ExpenseCode, r.RepLineDesc as [Desc]
--FROM #MappingTable m
--LEFT JOIN pbi.dim_MA_ExpenseCodes e
--On m.RepKey = e.Repkey
--LEFT JOIN pbi.dim_MA_Reports r
--ON r.RepKey = m.Map
--WHERE e.expensecode = 101
--ORDER BY m.map


SELECT DISTINCT e.ExpenseCode, e.ShortName, e.[Desc], e.AccountType,  r.RepLineDesc as [RepLine], m.map as Repkey
FROM #MappingTable m
LEFT JOIN pbi.dim_MA_ExpenseCodes e
On m.RepKey = e.Repkey
LEFT JOIN pbi.dim_MA_Reports r
ON r.RepKey = m.Map
WHERE e.ExpenseCode is not null --and r.CalcType is Null
