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

 

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

 

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,

 

Best Regards,
Mahmoud M. AlSaadi