-3

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
  • What version of SQL Server are you using? – aduguid Aug 17 '18 at 6:14
  • Microsoft SQL Server Management Studio 14.0.17213.0 Microsoft Analysis Services Client Tools 14.0.1016.232 Microsoft Data Access Components (MDAC) 10.0.14393.0 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 9.11.14393.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 6.3.14393 – Harish Mohan Aug 17 '18 at 6:17
  • To find out Sql Server's version, use select @@version. SSMS' and Client tool versions and such are often of little relevance. – vonPryz Aug 17 '18 at 12:24
1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.