Apply Pivot in SQL Server

Question

Apply Pivot in SQL Server

Viewing 7 posts - 16 through 21 (of 21 total)

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

Ombir SSC-Addicted Points: 466 More actions · Answer 1

Hi Twin Devil,

I have already completed these stairways and apply operator basics. Now I'm focusing on practical part and I know the best way to learn is solve problems that people posts on different forums.

Can you please answer my questions that I asked in previous post ???

I would be really grateful.

Thanks.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Just a small changes based on preferences. I prefer to make the FROM clause as simple as possible, that's why I use CTEs instead of derived tables (same thing different syntax). I added some simplicity to the CASE statement. And finally, I kept strings as strings and numbers as numbers.

WITH T1 AS

(

SELECT

PromotionInd, vChargeCode, [ChrgAmt]

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

FROM [dbo].[DtlChrg]

CROSS APPLY ( SELECT CASE

WHEN ChrgCode IN ('BRK', 'CLR', 'BIN', 'SDC')

THEN ChrgCode

ELSE 'OTHER' END AS vChargeCode) Adj

)

SELECT

PromotionInd

, ISNULL(MAX(CASE WHEN vChargeCode = 'BRK' THEN vChargeCode END),'BRK') AS BrkChargeCode

, ISNULL(SUM(CASE WHEN vChargeCode = 'BRK' THEN [ChrgAmt] END),0) AS BrkAmount

, ISNULL(MAX(CASE WHEN vChargeCode = 'CLR' THEN vChargeCode END),'CLR') AS CLRChargeCode

, ISNULL(SUM(CASE WHEN vChargeCode = 'CLR' THEN [ChrgAmt] END),0) AS CLRAmount

, ISNULL(MAX(CASE WHEN vChargeCode = 'BIN' THEN vChargeCode END),'BIN') AS BINChargeCode

, ISNULL(SUM(CASE WHEN vChargeCode = 'BIN' THEN [ChrgAmt] END),0) AS BINAmount

, ISNULL(MAX(CASE WHEN vChargeCode = 'SDC' THEN vChargeCode END),'SDC') AS SDCChargeCode

, ISNULL(SUM(CASE WHEN vChargeCode = 'SDC' THEN [ChrgAmt] END),0) AS SDCAmount

, ISNULL(SUM(CASE WHEN vChargeCode NOT IN ('BRK', 'CLR', 'BIN', 'SDC') then [ChrgAmt] end),0)AS OTHERAmount

FROM T1

GROUP BY

PromotionInd, PromotionRowID

ORDER BY

PromotionInd;

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 ?

The Select statement in the APPLY clause doesn't include a FROM clause because it's referencing a column from previous tables (in this case DtlChrg) and making computations on the columns instead of adding another data source.

"Adj" is the alias that he used for the derived table created with the APPLY operator and "vChargeCode" is the alias for the column. There's no rule on how to name them, but you need to name them somehow.

In short words, the APPLY would work very similar to a JOIN clause (CROSS APPLY = INNER JOIN, OUTER APPLY = LEFT JOIN) with the difference that you can reference columns from the previously defined tables. In this case it's just adding columns, but it could also add or remove rows, the same way as JOINs do.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ombir SSC-Addicted Points: 466 More actions · Answer 3

The Select statement in the APPLY clause doesn't include a FROM clause because it's referencing a column from previous tables (in this case DtlChrg) and making computations on the columns instead of adding another data source.
"Adj" is the alias that he used for the derived table created with the APPLY operator and "vChargeCode" is the alias for the column. There's no rule on how to name them, but you need to name them somehow.
In short words, the APPLY would work very similar to a JOIN clause (CROSS APPLY = INNER JOIN, OUTER APPLY = LEFT JOIN) with the difference that you can reference columns from the previously defined tables. In this case it's just adding columns, but it could also add or remove rows, the same way as JOINs do.

Hi Luis,

Thanks a lot for explanation. Looks like things are little bit clear now. As you said, In this case cross apply only just adding columns. Now as per my understanding we can avoid cross apply in this case because its only increasing complexity. The below code is also giving me the same output without apply operator.

Correct me if I'm wrong.

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,

CASE ChrgCode

WHEN 'BRK' THEN ChrgCode

WHEN 'CLR' THEN ChrgCode

WHEN 'BIN ' THEN ChrgCode

WHEN 'SDC'THEN ChrgCode

ELSE

'OTHER'

END AS vChargeCode

, [ChrgAmt]

, ROW_NUMBER() OVER (PARTITION BY PromotionInd,ChrgCode ORDER BY ChrgCode) as PromotionRowID

FROM

[dbo].[DtlChrg]

) T1

GROUP BY

PromotionInd, PromotionRowID

ORDER BY

PromotionInd

Regards,

Ombir

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

Apply Operator is to make the query less complex, rather then to make it more complex.

For Example:

If you would have required to have a aggregation on the vChargeCode instead of ChrgCode column you would be ending up something like this:

Option 1 : use the case statement in the SELECT clause as well as GROUP BY Clause.

---- Option 1

SELECT

PromotionInd,

CASE ChrgCode

WHEN 'BRK' THEN ChrgCode

WHEN 'CLR' THEN ChrgCode

WHEN 'BIN ' THEN ChrgCode

WHEN 'SDC'THEN ChrgCode

ELSE

'OTHER'

END AS vChargeCode

, SUM([ChrgAmt]) as SumOfChrgAmt

FROM

[dbo].[DtlChrg]

Group By PromotionInd

,(CASE ChrgCode

WHEN 'BRK' THEN ChrgCode

WHEN 'CLR' THEN ChrgCode

WHEN 'BIN ' THEN ChrgCode

WHEN 'SDC'THEN ChrgCode

ELSE

'OTHER'

END)

Option 2: Make a derived table

---- Option 2: Make a derived table

SELECT a.PromotionInd, a.vChargeCode, SUM(a.[ChrgAmt])

FROM

(

SELECT

PromotionInd,

CASE ChrgCode

WHEN 'BRK' THEN ChrgCode

WHEN 'CLR' THEN ChrgCode

WHEN 'BIN ' THEN ChrgCode

WHEN 'SDC'THEN ChrgCode

ELSE

'OTHER'

END AS vChargeCode

, [ChrgAmt]

FROM

[dbo].[DtlChrg]

) A

Group by a.PromotionInd, a.vChargeCode

Option 3: Use a CTE

---- Option 3: CTE Approach

WITH CTE

AS

(

SELECT

PromotionInd,

CASE ChrgCode

WHEN 'BRK' THEN ChrgCode

WHEN 'CLR' THEN ChrgCode

WHEN 'BIN ' THEN ChrgCode

WHEN 'SDC'THEN ChrgCode

ELSE

'OTHER'

END AS vChargeCode

, [ChrgAmt]

FROM

[dbo].[DtlChrg]

)

SELECT a.PromotionInd, a.vChargeCode, SUM(a.[ChrgAmt])

FROM CTE A

Group by a.PromotionInd, a.vChargeCode

Option 1, is more complex as you have to maintain the Case statement in Group by,

Option 2,3, they are very neat but have to use two select clause.

What if i want to do it in a single select ? Here comes Apply operator:

---- Option 4: Using Apply Operator

SELECT

PromotionInd, Adj.vChargeCode

, SUM([ChrgAmt]) AS SumOfChrgAmt

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

GROUP BY

PromotionInd, Adj.vChargeCode

Less code and neater then above.

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

Ombir (8/15/2016)
The Select statement in the APPLY clause doesn't include a FROM clause because it's referencing a column from previous tables (in this case DtlChrg) and making computations on the columns instead of adding another data source.
"Adj" is the alias that he used for the derived table created with the APPLY operator and "vChargeCode" is the alias for the column. There's no rule on how to name them, but you need to name them somehow.
In short words, the APPLY would work very similar to a JOIN clause (CROSS APPLY = INNER JOIN, OUTER APPLY = LEFT JOIN) with the difference that you can reference columns from the previously defined tables. In this case it's just adding columns, but it could also add or remove rows, the same way as JOINs do.
Hi Luis,
Thanks a lot for explanation. Looks like things are little bit clear now. As you said, In this case cross apply only just adding columns. Now as per my understanding we can avoid cross apply in this case because its only increasing complexity. The below code is also giving me the same output without apply operator.
Correct me if I'm wrong.
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,
CASE ChrgCode
WHEN 'BRK' THEN ChrgCode
WHEN 'CLR' THEN ChrgCode
WHEN 'BIN ' THEN ChrgCode
WHEN 'SDC'THEN ChrgCode
ELSE
'OTHER'
END AS vChargeCode
, [ChrgAmt]
, ROW_NUMBER() OVER (PARTITION BY PromotionInd,ChrgCode ORDER BY ChrgCode) as PromotionRowID
FROM
[dbo].[DtlChrg]
) T1
GROUP BY
PromotionInd, PromotionRowID
ORDER BY
PromotionInd
Regards,
Ombir

Well, you need to be changing your PromotionRowID logic as well, Other wise your whouldn't be having the same result. Let say you have another ChargeCode 'SLA' which comes under the 'OTHERS' column. Your result will not match your required output.

------ Insert statement a dummy Charge code 'SLA'

INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],[PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLA','N','999')

GO

Your current query will bring the following output:

PromotionInd BrkChargeCode BrkAmount CLRChargeCode CLRAmount BINChargeCode BINAmount SDCChargeCode SDCAmount OTHERAmount

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

N BRK 200.000000 CLR 250.000000 BIN 20.000000 SDC 552.000000 1244.000000

N BRK 55.000000 CLR 5.000000 BIN 25.000000 SDC 300.000000 220.000000

Y BRK 100.000000 CLR 140.000000 BIN 500.000000

You need to correct your query something like this: