0

I have a table data like the below with Type Regular/Irregular from January to December.

I need a query to do this data like the below table: January with below divided into two-jRegular & jIrregular. whole the year data using the PIVOT or Cross tab.

CREATE TABLE [dbo].[Details](   [ID] [int] IDENTITY(1,1) NOT NULL,  [Type] [nvarchar](10) NOT NULL,     [Header] [nvarchar](50) NOT NULL,   [SubHeader] [nvarchar](50) NOT NULL,    [January] [decimal](18, 2) NOT NULL,    [February] [decimal](18, 0) NOT NULL,   [March] [decimal](18, 2) NOT NULL,  [April] [decimal](18, 2) NOT NULL,  [May] [decimal](18, 2) NOT NULL,    [June] [decimal](18, 2) NOT NULL,   [July] [decimal](18, 2) NOT NULL ) ON [PRIMARY]

GO SET IDENTITY_INSERT [dbo].[Details] ON 

GO INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader], [January], [February], [March], [April], [May], [June], [July]) VALUES (2, N'Regular', N'Item-1', N'A.Sub-Item1', CAST(64.92 AS Decimal(18, 2)), CAST(57 AS Decimal(18, 0)), CAST(89.67 AS Decimal(18, 2)), CAST(99.20 AS Decimal(18, 2)), CAST(76.34 AS Decimal(18, 2)), CAST(23.33 AS Decimal(18, 2)), CAST(32.23 AS Decimal(18, 2))) GO INSERT [dbo].[Details] ([ID], [Type], [Header], [SubHeader], [January], [February], [March], [April], [May], [June], [July]) VALUES (3, N'Irregular', N'Item-1', N'A.Sub-Item1', CAST(43.45 AS Decimal(18, 2)), CAST(65 AS Decimal(18, 0)), CAST(65.88 AS Decimal(18, 2)), CAST(56.77 AS Decimal(18, 2)), CAST(45.65 AS Decimal(18, 2)), CAST(56.87 AS Decimal(18, 2)), CAST(67.55 AS Decimal(18, 2))) GO SET IDENTITY_INSERT [dbo].[Details] OFF GO

SELECT Header, SubHeader,
  Regular, Irregular,
  FRegular, FIrregular,
  MrRegular, MrIrregular
  FROM (SELECT Header, SubHeader, Type, 'F'+Type AS FType, 'Mr'+Type as MType, 'Ap'+Type AType, 'Ma'+Type as May,
  [January], [February], [March] 
  from [CashFlow].[dbo].[Details] ) p

  PIVOT (MAX(January) FOR Type in (Regular, Irregular)) AS JPVT
  PIVOT (MAX([February]) FOR FType in (FRegular, FIrregular)) AS FPVT
  PIVOT (MAX(March) FOR MType in (MrRegular, MrIrregular)) AS MrPVT

enter image description here

Any one can help me.

  • 3
    Hi and welcome to dba.se. Could you post the table create and insert statements? This will help in getting a correct answer faster. – Randi Vertongen Apr 19 at 9:00
  • 1
    As @RandiVertongen please use CREATE TABLE blah... and INSERT INTO blah VALUES... statements instead of screenshots. There are a few articles on how to answer questions in my profile - you might like to take a look. p.s. welcome to the forum! :-) – Vérace Apr 22 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.