Search This Blog

Monday, October 22, 2018

Splitting dimension into dimension columns in SQL.

Getting dimension into splitted values

For ledger dimension
Select Dimension,MainAccount,Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement from
 (
 select DIMENSIONATTRIBUTEVALUECOMBINATION.recid as dimension,DimensionAttribute.name,DimensionAttributeLevelValueAllView.DisplayValue from DIMENSIONATTRIBUTEVALUECOMBINATION
 join DimensionAttributeLevelValueAllView on DimensionAttributeLevelValueAllView.VALUECOMBINATIONRECID = DIMENSIONATTRIBUTEVALUECOMBINATION.RECID
 join DimensionAttribute on DIMENSIONATTRIBUTE.RecId = DimensionAttributeLevelValueAllView.DIMENSIONATTRIBUTE
 where DIMENSIONATTRIBUTEVALUECOMBINATION.MAINACCOUNT!=0
 ) d
PIVOT
(
 MAX(DisplayValue)
 FOR Name
 IN (MainAccount,Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement)
)
AS PivotTable





select DefaultDimension,Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement from
(
select DIMENSIONATTRIBUTEVALUECOMBINATION.recid as DefaultDimension,DimensionAttribute.name,DimensionAttributeValueSetItem.DisplayValue from DIMENSIONATTRIBUTEVALUECOMBINATION
join DimensionAttributeValueSet on DimensionAttributeValueSet.RecId = DIMENSIONATTRIBUTEVALUECOMBINATION.RECID
join DimensionAttributeValueSetItem on DimensionAttributeValueSetItem.DIMENSIONATTRIBUTEVALUESET = DimensionAttributeValueSet.RecID
join DimensionAttributeValue on DimensionAttributeValue.RECID = DimensionAttributeValueSetItem.DIMENSIONATTRIBUTEVALUE
join DimensionAttribute on DIMENSIONATTRIBUTE.RecId = DimensionAttributeValue.DIMENSIONATTRIBUTE
where DIMENSIONATTRIBUTEVALUECOMBINATION.MAINACCOUNT=0
) d
PIVOT
(
MAX(DisplayValue)
FOR Name
IN (Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement)
)
AS PivotTable