Data Structure transformation- MS server [closed]

Posted on

Question :

I kinda stuck at transforming the data structure, Please help!
This is what i have so far,

SELECT t0._id, t0.Category, MONTH(t0.TranDate) AS MONTHS, sum(t0.TranAmount) AS trans_total, 
                count(t0._id) AS trans_count, t0.TranBaseType

FROM [CreditSense].[dbo].[Transaction] AS t0
WHERE t0.TranDate IS NOT NULL
GROUP BY t0._id, t0.Category, MONTH(t0.TranDate), t0.Time_Of_Day, t0.TranBaseType
ORDER BY t0._id, t0.Category; 

I get this following out put
enter image description here

But What i really want is some thing like this,
enter image description here

This is just an illustration of the real data set. If you notice I names the columns based on the instances of the various combination of categorical variables. I really what this done automatically, like creating and naming the new columns. Please HELP!

WITH 
trasaction_data
AS
(
SELECT _id, ISNULL(Category,'NULL') AS Category, MONTH(TranDate) AS MONTHS, sum(TranAmount) AS trans_total, 
                count(_id) AS trans_count, TranBaseType

FROM [CreditSense].[dbo].[Transaction]
WHERE TranDate IS NOT NULL AND
 _id = '552d69d17d6a960016000001'
GROUP BY _id, Category, MONTH(TranDate), Time_Of_Day, TranBaseType
ORDER BY _id, Category
)
, 
transaction_data_format
AS
(
SELECT _id, ColumnHeader = Category + '.' + CAST(MONTHS AS varchar) + '.' + TranBaseType, trans_total
FROM trasaction_data
)

SELECT tsum._id,tsum.Benefits.1.Credit, tsum.Non-Periodic.2.Credit, 
        tsum.Other Income.3.Debit
FROM 
(
    SELECT _id, ColumnHeader, trans_total
    FROM transaction_data_format
    ) AS tdf

PIVOT 
    (
    SUM(tdf.trans_total)
    FOR tdf.ColumnHeader IN (Benefits.1.credit,Non-Periodic.2.Credit, Other Income.3.Debit)
    ) AS tsum

Answer :

You could use the PIVOT to achieve this.

WITH
transaction_data
AS
(
    SELECT tbl.* FROM (VALUES
      ( 1, 'Benefits', 1, 22, 4, 'Credit')
    , ( 1, 'Non-Periodic', 2, 35, 5, 'Credit')
    , ( 1, 'Other Income', 3, 45, 3, 'Debit')
    , ( 2, 'Benefits', 1, 188, 9, 'Credit')
    , ( 2, 'Other Income', 2, 34, 3, 'Debit')
    , ( 3, 'Non-Periodic', 1, 65, 5, 'Credit')
    , ( 4, 'Benefits', 2, 34, 3, 'Debit')
    ) tbl ([_id], [Category], [Months], [trans_total], [trans_count], [TranBase Type]) 
)
, 
transaction_data_format
AS
(
SELECT 
      [_id]
    , [ColumnHeader] = [Category] + '.' + CAST([Months] AS VARCHAR) + '.' + [TranBase Type]
    , [trans_total]
FROM 
    transaction_data
)
SELECT 
      tsum.[_id]
    , tsum.[Benefits.1.Credit]
    , tsum.[Non-Periodic.2.Credit]
    , tsum.[Other Income.3.Debit]
FROM 
    (
        SELECT
              [_id]
            , [ColumnHeader]
            , [trans_total]
        FROM
            transaction_data_format
    ) AS tdf
PIVOT
    (
    SUM(tdf.[trans_total]) 
    FOR tdf.[ColumnHeader] IN ([Benefits.1.Credit], [Non-Periodic.2.Credit], [Other Income.3.Debit])
    ) AS tsum

screenshot

Leave a Reply

Your email address will not be published.