Sql-server – Apply pivot on SQL Server table

pivotsql server

I have a table which gives the output when queried normally. I want to transform rows to columns and columns to rows to show it in a report.

I want the tranformation to be applied as shown in the below image.
Desired Output Image
Other than BRk, CLR, BIN and SDC, they need to be categorized under Others in the above desired table.

Below is the table's DDL and DML.

  SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[DtlChrg](
  [ID] [uniqueidentifier] NOT NULL,
  [ChrgCode] [nvarchar](10) NULL,
  [PromotionInd] [nvarchar](1) NULL,
  [ChrgAmt] [decimal](15, 6) NULL

  CONSTRAINT [PK_DtlChrg] PRIMARY KEY CLUSTERED 
  (
  [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
  IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO


   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','Y','100')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','N','200')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','N','55')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','Y','140')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','N','250')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','N','5')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','Y','100')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','N','300')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','N','552')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','Y','500')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','N','20')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','N','25')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','Y','540')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','N','220')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','N','245')

Thanks..

Best Answer

If you don't know in advance the name of the columns you want to put in your 'Other' column, you have to use something else in addition to the PIVOT statement.

You can try something like :

select  t.PromotionInd  as [Promo Id],
        'BRK'           as [Charge ID],
        t.[BRK]         as [Amount],
        'CLR'           as [Charge ID],
        t.[CLR]         as [Amount],
        'SDC'           as [Charge ID],
        t.[SDC]         as [Amount],
        'BIN'           as [Charge ID],
        t.[BIN]         as [Amount],
        'Other'         as [Charge ID],
        o.Amount        as [Amount]
from (
    select PromotionInd, sum(chrgAmt) as Amount
    from DtlChrg
    where ChrgCode not in ('CLR', 'SDC', 'BRK', 'BIN')
    group by PromotionInd
    ) o
join (
   select p.PromotionInd, p.BIN, p.BRK, p.CLR, p.SDC
   from (
       select PromotionInd, ChrgCode, chrgAmt
       from DtlChrg
        ) s
   pivot
       (
       sum(chrgAmt)
       for ChrgCode in ([CLR], [SDC], [BRK], [BIN])
       ) p
   ) t on o.PromotionInd = t.PromotionInd

(but not very nice to have several columns with the same name or with spaces inside...)

(and you can't have a "double header" as in your example, this is something you should build in your reporting tool, not directly in SQL I think)

Related Question