Apply Pivot in SQL Server

Question

Apply Pivot in SQL Server

User7766

SSCommitted

Points: 1969
More actions
August 10, 2016 at 3:19 am

#328863

Hi All,
I've 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.
PromotionIn ChargeCode Amount
--------------------------------------
Y CLR 200
N CLR 50
Y BRK 0
Y SDC 50
Y BIN 100
Y BRK 200
Y SDC 20
Y BIN 9
Y SLC 2
Y SOLC 45
Y SOC 100
N CLR 1200
I want the above table to transfor it to the below without aggregate function.
PromotionIn BRK CLR BIN SDC Others
ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount
-------------------------------------------------------------------------------------------------------------
Y BRK 200 CLR 200 CLR 200 CLR 200
N BRK 200 CLR 200 CLR 200 CLR 200
Y BRK 200 CLR 200 CLR 200 CLR 200
Y BRK 200 CLR 200 CLR 200 CLR 200
Other than BRk, CLR, BIN and SDC, they need to be categorized under Others in the above desired table.
Can anyone please help me on this.
Thanks..

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

User7766 (8/10/2016)
Hi All,
I've 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.
PromotionIn ChargeCode Amount
--------------------------------------
Y CLR 200
N CLR 50
Y BRK 0
Y SDC 50
Y BIN 100
Y BRK 200
Y SDC 20
Y BIN 9
Y SLC 2
Y SOLC 45
Y SOC 100
N CLR 1200
I want the above table to transfor it to the below without aggregate function.
PromotionIn BRK CLR BIN SDC Others
ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount
-------------------------------------------------------------------------------------------------------------
Y BRK 200 CLR 200 CLR 200 CLR 200
N BRK 200 CLR 200 CLR 200 CLR 200
Y BRK 200 CLR 200 CLR 200 CLR 200
Y BRK 200 CLR 200 CLR 200 CLR 200
Other than BRk, CLR, BIN and SDC, they need to be categorized under Others in the above desired table.
Can anyone please help me on this.
Thanks..

can you please explain your logic .......every 'Amount' in the pivot is 200?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

you Output table is not conclusive, Kindly share the output data according to your sample data.

User7766 SSCommitted Points: 1969 More actions · Answer 3

Hi,

Thanks for the reply. My sincere apologies, It is a data error while copying and pasting. I've multiple types of ChargeCodes and the charge amounts. I want all the chargecodes and amounts to be displayed as columns irrespective of what the amount is. No need to sum or do anything.

Other than the chargecodes (BRK, CLR, SDC, BIN), need to be categorized under 1 column called "Other Charges" and i need only ChargeAmount there to display.

I hope I'm clear.. Kindly please let me know if you need anything else from me,.

User7766 SSCommitted Points: 1969 More actions · Answer 4

twin.devil (8/10/2016)
you Output table is not conclusive, Kindly share the output data according to your sample data.

Hi Twin.Devil,

Here is the desired output I'm expecting from the input data that i've given in the starting thread.

EDIT : Here is the link for the original record and the desired output images. Please help me on this..

PromotionIn BRK CLR BIN SDC Others

ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount

-------------------------------------------------------------------------------------------------------------

Y BRK 200 CLR 200 CLR 200 CLR 200

N BRK 200 CLR 200 CLR 200 CLR 200

Y BRK 200 CLR 200 CLR 200 CLR 200

BRK, CLR, BIN, SDC and other chargecodes, ChargeAmounts are rows in the first table, I need to display these ChargeCodes seperately as columns with respective chargeamounts for each chargetype.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

User7766 (8/10/2016)
twin.devil (8/10/2016)
you Output table is not conclusive, Kindly share the output data according to your sample data.
Hi Twin.Devil,
Here is the desired output I'm expecting from the input data that i've given in the starting thread.
PromotionIn BRK CLR BIN SDC Others
ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount ChargeCOde Amount
-------------------------------------------------------------------------------------------------------------
Y BRK 200 CLR 200 CLR 200 CLR 200
N BRK 200 CLR 200 CLR 200 CLR 200
Y BRK 200 CLR 200 CLR 200 CLR 200
Y BRK 200 CLR 200 CLR 200 CLR 200
BRK, CLR, BIN, SDC and other chargecodes, ChargeAmounts are rows in the first table, I need to display these ChargeCodes seperately as columns with respective chargeamounts for each chargetype.

its the same as above, not conclusive. Please read this to properly post on a forum

User7766 SSCommitted Points: 1969 More actions · Answer 6

In the previous post, I've uploaded the images to onedrive, Please have a look at it.. I dont know how to explain this, But i need to transform all the chargecodes and chargeamounts to be columns like

Promo IndCharge IDAmountCharge IDAmountCharge IDAmountCharge IDAmountAmount

Y BRK 100 CLR 50 SDC 40 BIN 120 0

N BRK 200 CLR 60 SDC 30 BIN 90 10

In the original output, ChargeID and the Amount were rows. But I need them to be displayed in the above format. Hope it is clear now..

User7766 SSCommitted Points: 1969 More actions · Answer 7

Table:

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

Data for the table

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')

Select statement(Normal one)

SELECT * from DtlChrg

PIVOT (Desired Output) PLease see my above post iamge

Umer Akram SSC-Insane Points: 22209 More actions · Answer 8

SELECT

PromotionInd

, ISNULL(SUM(case when vChargeCode = 'BRK' then [ChrgAmt] end),0) AS BrkAmount

, ISNULL(SUM(case when vChargeCode = 'CLR' then [ChrgAmt] end),0) AS CLRAmount

, ISNULL(SUM(case when vChargeCode = 'BIN' then [ChrgAmt] end),0) AS BINAmount

, ISNULL(SUM(case when vChargeCode = 'SDC' then [ChrgAmt] end),0) AS SDCAmount

, ISNULL(SUM(case when vChargeCode = 'OTHER' then [ChrgAmt] end),0)AS OTHERAmount

FROM

(

SELECT

PromotionInd, [ChrgAmt], Adj.vChargeCode

FROM

[dbo].[DtlChrg]

CROSS APPLY

(

SELECT CASE ChrgCode

WHEN 'BRK' THEN ChrgCode

WHEN 'CLR' THEN ChrgCode

WHEN 'BIN ' THEN ChrgCode

WHEN 'SDC'THEN ChrgCode

ELSE

'OTHER'

END AS vChargeCode

) Adj

) T1

GROUP BY

PromotionInd

User7766 SSCommitted Points: 1969 More actions · Answer 9

Hi,

Thanks for the query, Few changes, I'm sorry for that..

I dont want the SUM of all amounts, I just need individual amounts and also the ChargeId column like in the image attached..

PromotionInd BRKCHargeCode BRKAmount CLRCHargeCode CLRAMount ..

Umer Akram SSC-Insane Points: 22209 More actions · Answer 10

SELECT

PromotionInd

, ISNULL(MAX(case when vChargeCode = 'BRK' then vChargeCode end),0) AS BrkChargeCode

, ISNULL(SUM(case when vChargeCode = 'BRK' then [ChrgAmt] end),0) AS BrkAmount

, ISNULL(MAX(case when vChargeCode = 'CLR' then vChargeCode end),0) AS CLRChargeCode

, ISNULL(SUM(case when vChargeCode = 'CLR' then [ChrgAmt] end),0) AS CLRAmount

, ISNULL(MAX(case when vChargeCode = 'BIN' then vChargeCode end),0) AS BINChargeCode

, ISNULL(SUM(case when vChargeCode = 'BIN' then [ChrgAmt] end),0) AS BINAmount

, ISNULL(MAX(case when vChargeCode = 'SDC' then vChargeCode end),0) AS SDCChargeCode

, ISNULL(SUM(case when vChargeCode = 'SDC' then [ChrgAmt] end),0) AS SDCAmount

, ISNULL(SUM(case when vChargeCode = 'OTHER' then [ChrgAmt] end),0)AS OTHERAmount

FROM

(

SELECT

PromotionInd, Adj.vChargeCode, [ChrgAmt]

, ROW_NUMBER() OVER (PARTITION BY PromotionInd, Adj.vChargeCode ORDER BY Adj.vChargeCode) as PromotionRowID

FROM

[dbo].[DtlChrg]

CROSS APPLY

(

SELECT CASE ChrgCode

WHEN 'BRK' THEN ChrgCode

WHEN 'CLR' THEN ChrgCode

WHEN 'BIN ' THEN ChrgCode

WHEN 'SDC'THEN ChrgCode

ELSE

'OTHER'

END AS vChargeCode

) Adj

) T1

GROUP BY

PromotionInd, PromotionRowID

ORDER BY

PromotionInd

User7766 SSCommitted Points: 1969 More actions · Answer 11

twin.devil (8/10/2016)
SELECT PromotionInd , ISNULL(MAX(case when vChargeCode = 'BRK' then vChargeCode end),0) AS BrkChargeCode , ISNULL(SUM(case when vChargeCode = 'BRK' then [ChrgAmt] end),0) AS BrkAmount , ISNULL(MAX(case when vChargeCode = 'CLR' then vChargeCode end),0) AS CLRChargeCode , ISNULL(SUM(case when vChargeCode = 'CLR' then [ChrgAmt] end),0) AS CLRAmount , ISNULL(MAX(case when vChargeCode = 'BIN' then vChargeCode end),0) AS BINChargeCode , ISNULL(SUM(case when vChargeCode = 'BIN' then [ChrgAmt] end),0) AS BINAmount , ISNULL(MAX(case when vChargeCode = 'SDC' then vChargeCode end),0) AS SDCChargeCode , ISNULL(SUM(case when vChargeCode = 'SDC' then [ChrgAmt] end),0) AS SDCAmount , ISNULL(SUM(case when vChargeCode = 'OTHER' then [ChrgAmt] end),0)AS OTHERAmount FROM ( SELECT PromotionInd, Adj.vChargeCode, [ChrgAmt] , ROW_NUMBER() OVER (PARTITION BY PromotionInd, Adj.vChargeCode ORDER BY Adj.vChargeCode) as PromotionRowID FROM [dbo].[DtlChrg] CROSS APPLY ( SELECT CASE ChrgCode WHEN 'BRK' THEN ChrgCode WHEN 'CLR' THEN ChrgCode WHEN 'BIN ' THEN ChrgCode WHEN 'SDC'THEN ChrgCode ELSE 'OTHER' END AS vChargeCode ) Adj ) T1 GROUP BY PromotionInd, PromotionRowID ORDER BY PromotionInd

Hi,

This worked fine.. If need to add a where condition, how can i add?

Thanks.

pietlinden SSC Guru Points: 63484 More actions · Answer 12

Right here...

) T1

WHERE <filters>

GROUP BY

It goes after the FROM clause and before the GROUP BY clause

Ombir Mr or Mrs. 500 Points: 526 More actions · Answer 13

User7766 (8/10/2016)

twin.devil (8/10/2016)


SELECT
PromotionInd
, ISNULL(MAX(case when vChargeCode = 'BRK' then vChargeCode end),0) AS BrkChargeCode
, ISNULL(SUM(case when vChargeCode = 'BRK' then [ChrgAmt] end),0) AS BrkAmount
, ISNULL(MAX(case when vChargeCode = 'CLR' then vChargeCode end),0) AS CLRChargeCode
, ISNULL(SUM(case when vChargeCode = 'CLR' then [ChrgAmt] end),0) AS CLRAmount
, ISNULL(MAX(case when vChargeCode = 'BIN' then vChargeCode end),0) AS BINChargeCode
, ISNULL(SUM(case when vChargeCode = 'BIN' then [ChrgAmt] end),0) AS BINAmount
, ISNULL(MAX(case when vChargeCode = 'SDC' then vChargeCode end),0) AS SDCChargeCode
, ISNULL(SUM(case when vChargeCode = 'SDC' then [ChrgAmt] end),0) AS SDCAmount
, ISNULL(SUM(case when vChargeCode = 'OTHER' then [ChrgAmt] end),0)AS OTHERAmount
FROM
(
SELECT
PromotionInd, Adj.vChargeCode, [ChrgAmt]
, ROW_NUMBER() OVER (PARTITION BY PromotionInd, Adj.vChargeCode ORDER BY Adj.vChargeCode) as PromotionRowID
FROM
[dbo].[DtlChrg]
CROSS APPLY
(
SELECT CASE ChrgCode
WHEN 'BRK' THEN ChrgCode
WHEN 'CLR' THEN ChrgCode
WHEN 'BIN ' THEN ChrgCode
WHEN 'SDC'THEN ChrgCode
ELSE
'OTHER'
END AS vChargeCode
) Adj
) T1
GROUP BY
PromotionInd, PromotionRowID
ORDER BY
PromotionInd

Hi Twin Devil,

I'm learning T-Sql and having hard time to understand how this query works. Every time I see the apply operator in forums, its being applied with different logic.

Like in this case, Select statement after cross apply doesn't include from clause.

Can you please tell how this whole query is working means which steps is evaluated first then next step.

I'm also confused with these column aliases like "Adj.vChargeCode" and particularly this Select case statement after Cross apply operator means how you're referencing this ChrgCode column without any from clause ?

Please help ?

Thank you.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 14

I'm learning T-Sql and having hard time to understand how this query works. Every time I see the apply operator in forums, its being applied with different logic.

Well, If you are learning T-SQL then you are in a great place to learn it. All you need to do is to look around a bit.

Gregory Larsen have written stairways for the learning of T-SQL for the new folks ( And older too :-P).

For Basic level Stairway to T-SQL: Beyond The Basics[/url]

For next level Stairway to Advanced T-SQL[/url]

For the understanding and utilization of APPLY operator you need to read this:

Stairway to Advanced T-SQL Level 2: Using the APPLY Operator [/url]

Like in this case, Select statement after cross apply doesn't include from clause. Can you please tell how this whole query is working means which steps is evaluated first then next step.
I'm also confused with these column aliases like "Adj.vChargeCode" and particularly this Select case statement after Cross apply operator means how you're referencing this ChrgCode column without any from clause ?
Please help ?
Thank you.

For this question, please read this excellent article Using APPLY to make your queries DRYer[/url] by Gerald Britton

Hope it helps.