Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Thursday, August 14, 2014

TSQL Fun (Pivot Function) – Analytical Accounting Dimensions

As part of a personal project I have been working on, I needed to change the structure of the data set retrieved by a simple join TSQL query, I ended up creating a dynamic code to automatically change the data structure for Analytical Accounting dimensions.

Data Structure

Data Structure

 

Methodology

To get the first result on the left, a simple join is required between AAG00400 (Transaction Dimension Master) and AAG00401(Transaction Dimension Code Setup).

SELECT  A.aaTrxDim ,
       
B.aaTrxDimCode
        FROM    AAG00400 AS A
       
LEFT OUTER JOIN AAG00401 AS B ON A.aaTrxDimID = b.aaTrxDimID

 

JoinDimensions

I will review two methods to switch the rows into columns, a static Pivot and dynamic one.

Static Method

In this case, I should include a hard-coded values within the SQL statement in order to switch the rows into columns. Which means, when a new dimension is added, it will not “automatically” appear in the result unless the code is modified.

SELECT  *
FROM
( SELECT *
 
FROM    ( SELECT 
              
ROW_NUMBER() OVER
              
( PARTITION BY A.aaTRXDIM ORDER BY B.aaTrxDimCode ) 
                 AS DimensionCodeRank,
                 A.aaTrxDim ,
                 B.aaTrxDimCode
                
FROM AAG00400 AS A
                 LEFT OUTER JOIN AAG00401 AS B
                 ON A.aaTrxDimID = b.aaTrxDimID
                 ) P PIVOT 
    
( MAX(aaTRXDIMCode) FOR aaTRXDIM IN 
                          
( [COSTCENTER], [EXPENSETYPE],
                            
[LOCATION], [ORGANAZIONAL LEVEL],
                            
[PROJECT], [REGION], [SECTOR] ) ) AS PVT
          
) AS AB

As applied above, the dimension codes are hard-coded into the SQL statement, which makes this method improper for dynamic solution. A similar PIVOT function has been used in a previous post to manipulate inventory balances, IV Available Quantity Report – SQL Pivot Function

Dynamic Method

Through this method, any dimension added on the Analytical Accounting module will be automatically retrieved, there is no need at all to modify the statement.

DECLARE @columns NVARCHAR(MAX) ,
       
@columns_n NVARCHAR(MAX) ,
       
@Statement NVARCHAR(MAX);
SET @columns = N'';
SET @columns_n = N'';
SELECT  @columns += N', X.' + QUOTENAME(aaTrxDim)
FROM    ( SELECT    RTRIM(aaTrxDim) AS aaTrxDim
         
FROM      dbo.AAG00400
        
) AS Y;
SELECT  @columns_n +=
        N', ISNULL(' + N'X.' + QUOTENAME(aaTrxDim)
+ N', '''' ) AS'
        + QUOTENAME(aaTrxDim)
FROM    ( SELECT    RTRIM(aaTrxDim) AS aaTrxDim
FROM      dbo.AAG00400
) AS Y;

SET
@Statement = N'
SELECT ' + STUFF(@columns_n, 1, 2, '') + '
FROM
(
    SELECT   ROW_NUMBER()
    over(partition by  A.aaTRXDIM order by B.aaTrxDimCode) AS DimensionCodeRank,
    A.aaTrxDim,B.aaTrxDimCode FROM AAG00400 AS A LEFT OUTER JOIN AAG00401 AS B
    ON A.aaTrxDimID = b.aaTrxDimID
) AS j

PIVOT
(
MAX(aaTRXDIMCode) FOR aaTRXDIM IN  (' + STUFF(REPLACE(@columns, ', X.[', ',['),1, 1, '') + ')) AS X;';

PRINT @Statement;
EXEC sp_executesql @Statement;

Here is the result for both of the method above,

Manipulated Data Structure

 

Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment