An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

Question

An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

Viewing 15 posts - 16 through 30 (of 46 total)

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

gsimard (8/2/2012)
Thanks for the article, very interresting.
Basically, your "other Unpivot" technique is a brillant way to use the new (from 2008) Table Value Constructor
Using the same method, from your first example you could replace :
INSERT INTO #Orders
SELECT 1, NULL, 3 UNION ALL SELECT 2, 5, 4 UNION ALL SELECT 1, 3, 10
By the following :
INSERT INTO #Orders VALUES
(1, NULL, 3), (2, 5, 4), (1, 3, 10)
I don't think it's a real gain but maybe it's more readable.

Yes, Table Value Constructor is the "official" name for the construction, whereas being the maverick that I tend to be I prefer calling them simply "VALUES sets." 🙂

For some reason, I generally don't use the Table Value Constructors on INSERTs for multiple records. Perhaps its because I post on the forum so much, and when I started I was really annoyed at people that did because my test bed was SQL 2005 in the beginning. I'd always have to restructure the INSERT so I could work out a solution.

Perhaps one day I'll change over.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86908 More actions · Answer 2

Cadavre, ChrisM, WayneS and jfogel -

Your positive comments are much appreciated. I was really hoping that few people had actually seen this approach because it does seem to be well-buried. I hadn't seen anyone use it on the forum until I recently started to (after finding out about it).

In any event, whichever way you choose to go, testing is appropriate to see how it performs on your actual data. While I said there was no difference in improvement across the various sparseness levels, there was a lot of increase in both CPU and Elapsed times as sparseness decreased.

So the old saying "test, test, test!" most certainly applies here.

Caveat Emptor!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86908 More actions · Answer 3

Mike McIver (8/2/2012)
Good to know ... I was unawares.
Here's the last example done with pivot/unpivot ... a bit more code, performance not as good ... but much more maintainable/flexible (IMO) given the problem setup ... generalizes the computations and can adjust for the source month columns coming in and out of use. In such a real world situation Users would likely accept a performance goal of tolerable ...
Those of you who continue to profess a belief in the Users will receive the standard substandard training which will result in your eventual elimination. Tron (1982)
;WITH [CTE_DETAIL]([ID],[ProductLine],[JanRev],[JanExp],[FebRev],[FebExp],[MarRev],[MarExp],[AprRev],[AprExp],[MayRev],[MayExp],[JunRev],[JunExp],[JulRev],[JulExp],[AugRev],[AugExp],[SepRev],[SepExp],[OctRev],[OctExp],[NovRev],[NovExp],[DecRev],[DecExp])
AS
(
SELECT[ID],
[ProductLine],
--$0 AS
[JanRev],
--$0 AS
[JanExp],
--$0 AS
[FebRev],
--$0 AS
[FebExp],
--$0 AS
[MarRev],
--$0 AS
[MarExp],
$0 AS
[AprRev],
$0 AS
[AprExp],
$0 AS
[MayRev],
$0 AS
[MayExp],
$0 AS
[JunRev],
$0 AS
[JunExp],
$0 AS
[JulRev],
$0 AS
[JulExp],
$0 AS
[AugRev],
$0 AS
[AugExp],
$0 AS
[SepRev],
$0 AS
[SepExp],
$0 AS
[OctRev],
$0 AS
[OctExp],
$0 AS
[NovRev],
$0 AS
[NovExp]
,
$0 AS
[DecRev],
$0 AS
[DecExp]
FROM#ProfitLoss
),
[CTE_DETAIL_UNPIVOTED]([ID],[ProductLine],[sMONTH],[iMONTH],[sACCT],[AMOUNT])
AS
(
SELECT[ID],
[ProductLine],
LEFT([PERIOD_ITEM], 3) AS [sMONTH],
MONTH(LEFT([PERIOD_ITEM], 3) + '1, 2000') AS [iMONTH],
RIGHT([PERIOD_ITEM], LEN([PERIOD_ITEM]) - 3) AS [sACCT],
[AMOUNT]
FROM(
SELECT[ID],
[ProductLine],
[JanRev],
[JanExp],
[JanRev]-[JanExp] AS [JanMargin],
[FebRev],
[FebExp],
[FebRev]-[FebExp] AS [FebMargin],
[MarRev],
[MarExp],
[MarRev]-[MarExp] AS [MarMargin],
[AprRev],
[AprExp],
[AprRev]-[AprExp] AS [AprMargin],
[MayRev],
[MayExp],
[MayRev]-[MayExp] AS [MayMargin],
[JunRev],
[JunExp],
[JunRev]-[JunExp] AS [JunMargin],
[JulRev],
[JulExp],
[JulRev]-[JulExp] AS [JulMargin],
[AugRev],
[AugExp],
[AugRev]-[AugExp] AS [AugMargin],
[SepRev],
[SepExp],
[SepRev]-[SepExp] AS [SepMargin],
[OctRev],
[OctExp],
[OctRev]-[OctExp] AS [OctMargin],
[NovRev],
[NovExp],
[NovRev]-[NovExp] AS [NovMargin],
[DecRev],
[DecExp],
[DecRev]-[DecExp] AS [DecMargin]
FROM[CTE_DETAIL]
) AS [PIVOTED] UNPIVOT
(
[AMOUNT] FOR [PERIOD_ITEM] IN
(
[JanRev],
[JanExp],
[JanMargin],
[FebRev],
[FebExp],
[FebMargin],
[MarRev],
[MarExp],
[MarMargin],
[AprRev],
[AprExp],
[AprMargin],
[MayRev],
[MayExp],
[MayMargin],
[JunRev],
[JunExp],
[JunMargin],
[JulRev],
[JulExp],
[JulMargin],
[AugRev],
[AugExp],
[AugMargin],
[SepRev],
[SepExp],
[SepMargin],
[OctRev],
[OctExp],
[OctMargin],
[NovExp],
[NovRev],
[NovMargin],
[DecRev],
[DecExp],
[DecMargin]
)
) AS [UNPIVIOTED]
),
[CTE_DETAIL_CALC]
(
[ID],
[ProductLine],
[sMONTH],
[sACCT],
[AMOUNT],
[ $ O/(U) Prev Mo],
[ % O/(U) Prev Mo]
)
AS
(
SELECTA.[ID],
A.[ProductLine],
A.[sMONTH],
A.[sACCT],
A.[AMOUNT],
COALESCE(A.[AMOUNT] - B.[AMOUNT], $0) AS [ $ O/(U) Prev Mo],
ROUND(100*COALESCE((A.[AMOUNT] - B.[AMOUNT])/NULLIF(B.[AMOUNT],$0),$0), 1) AS [ % O/(U) Prev Mo]
FROM[CTE_DETAIL_UNPIVOTED] A LEFT JOIN
[CTE_DETAIL_UNPIVOTED] B
ONA.[ID] = B.[ID]
ANDA.[sACCT] = B.[sACCT]
ANDA.[iMONTH] = B.[iMONTH] + 1
)
SELECT[ID],
[ProductLine],
[LineItem],
MAX([Jan]) AS [Jan],
MAX([Feb]) AS [Feb],
MAX([Mar]) AS [Mar],
MAX([Apr]) AS [Apr],
MAX([May]) AS [May],
MAX([Jun]) AS [Jun],
MAX([Jul]) AS [Jul],
MAX([Aug]) AS [Aug],
MAX([Sep]) AS [Sep],
MAX([Oct]) AS [Oct],
MAX([Nov]) AS [Nov],
MAX([Dec]) AS [Dec]
FROM(
SELECT[ID],
[ProductLine],
[sACCT],
CASE [sACCT]
WHEN 'Rev' THEN 'Revenues'
WHEN 'Exp' THEN 'Expenses'
WHEN 'Margin' THEN 'Margin'
ELSE 'UNKNOWN'
END AS [LineItem],
[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM(SELECT[ID], [ProductLine], [sMONTH], [sACCT], [AMOUNT], [ $ O/(U) Prev Mo], [ % O/(U) Prev Mo]FROM [CTE_DETAIL_CALC]) AS [UNPIVOTED] PIVOT
(MAX([AMOUNT]) FOR [sMONTH] IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS [PIVOTED_AMOUNT]
UNION
SELECT[ID],
[ProductLine],
[sACCT],
' $ O/(U) Prev Mo' AS [LineItem],
[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM(SELECT[ID], [ProductLine], [sMONTH], [sACCT], [AMOUNT], [ $ O/(U) Prev Mo], [ % O/(U) Prev Mo]FROM [CTE_DETAIL_CALC]) AS [UNPIVOTED] PIVOT
(MAX([ $ O/(U) Prev Mo]) FOR [sMONTH] IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS [PIVOTED_AMOUNT]
UNION
SELECT[ID],
[ProductLine],
[sACCT],
' % O/(U) Prev Mo' AS [LineItem],
[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM(SELECT[ID], [ProductLine], [sMONTH], [sACCT], [AMOUNT], [ $ O/(U) Prev Mo], [ % O/(U) Prev Mo]FROM [CTE_DETAIL_CALC]) AS [UNPIVOTED] PIVOT
(MAX([ % O/(U) Prev Mo]) FOR [sMONTH] IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS [PIVOTED_AMOUNT]
) A
GROUP BY [ID],[ProductLine],[LineItem],[sACCT]
ORDER BY
[ID],
[ProductLine],
CASE [sACCT]
WHEN 'Rev' THEN 10
WHEN 'Exp' THEN 20
WHEN 'Mar' THEN 30
ELSE 1000
END,
CASE [LineItem]
WHEN 'Revenues' THEN 10
WHEN 'Expenses' THEN 10
WHEN 'Margin' THEN 10
WHEN ' $ O/(U) Prev Mo' THEN 20
WHEN ' % O/(U) Prev Mo' THEN 30
ELSE 1000
END

And finally to Mike -

I am happy to see someone that rose to my challenge.

I'm not sure I'd agree with you on which is more maintainable. My general feeling is that the less code there is the more maintainable it will be (but not always). But differing opinions (and the ability to hold and defend them) is what makes this forum great.

Thanks for the contribution.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

cliffb (8/2/2012)
There is a way to get NULL values back in an unpivot. It's not pretty but it works.
IF OBJECT_ID('tempdb..#Orders','U') IS NOT NULL
DROP TABLE #Orders
-- DDL and sample data for UNPIVOT Example 1
CREATE TABLE #Orders
(Orderid int identity, GiftCard int, TShirt int, Shipping int)
INSERT INTO #Orders
SELECT 1, NULL, 3 UNION ALL SELECT 2, 5, 4 UNION ALL SELECT 1, 3, 10
SELECT * FROM #Orders
DECLARE @Start DATETIME
DECLARE @End DATETIME
SET @Start = GETDATE()
-- Traditional UNPIVOT
SELECT OrderID, convert(varchar(15), ProductName) [ProductName], ProductQty
FROM (
SELECT OrderID, GiftCard, TShirt, Shipping
FROM #Orders) p
UNPIVOT
(ProductQty FOR ProductName IN ([GiftCard], [TShirt], [Shipping])) as unpvt
SET @End = GETDATE()
SELECT DATEDIFF(ms,@Start,@End) AS ElapsedTime
-- Set a placeholder for NULL values.
-- Let's assume, the integers are always greater than zero.
-- if this is the case, we can use 0 to represent a NULL
DECLARE @NULL INT
SET @NULL = 0
SET @Start = GETDATE()
SELECT OrderID, convert(varchar(15), ProductName) [ProductName], NULLIF(ProductQty,0) AS ProductQty
FROM (
SELECT OrderID
, COALESCE(GiftCard,@NULL) AS GiftCard
, COALESCE(TShirt,@NULL) AS TShirt
, COALESCE(Shipping,@NULL) AS Shipping
FROM #Orders) p
UNPIVOT
(ProductQty FOR ProductName IN ([GiftCard], [TShirt], [Shipping])) as unpvt
SET @End=GETDATE()
SELECT DATEDIFF(ms,@Start,@End) AS ElapsedTime
Like i said, it's not pretty but will return your NULL values and in this case is faster than the traditional method on my laptop.

Cliff - I figured there must be a way to do it. Just couldn't be bothered at the time with figuring it out. And I agree it is definitely not pretty enough to want to take to the prom!

Edited: I originally asked if you were saying that it is faster than UNPIVOT when not attempting to bring the NULLs back or faster than CROSS APPLY VALUES, however a closer look shows you were timing against the UNPIVOT. I'm not sure with that size of a test harness, the results are good enough to take to the bank though.

Regardless, some may say that using the COALESCE as you have done "perverts" the natural information in the table. If the results are OK for the case though, I'd say that is marvelous. 😀

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Mike McIver SSC-Addicted Points: 493 More actions · Answer 5

I'm not sure I'd agree with you on which is more maintainable.

... May we see a "full year" version of the cross apply? Then we'll start adding years ... then the User wants year over year, qtr over qtr, etc, etc.

Mike
MacroTrenz.Com
LinkedIn

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

Mike McIver (8/3/2012)
I'm not sure I'd agree with you on which is more maintainable.
... May we see a "full year" version of the cross apply? Then we'll start adding years ... then the User wants year over year, qtr over qtr, etc, etc.

A challenge? I love a good challenge!

So let's first construct a new table and test harness that has 24 monthly buckets (1 each for Revenue and Expense):

IF OBJECT_ID('tempdb..#ProfitLoss','U') IS NOT NULL

DROP TABLE #ProfitLoss

-- DDL and sample data for UNPIVOT Example 3

CREATE TABLE #ProfitLoss

(ID INT IDENTITY, ProductLine VARCHAR(500)

,JanRev MONEY, FebRev MONEY, MarRev MONEY

,AprRev MONEY, MayRev MONEY, JunRev MONEY

,JulRev MONEY, AugRev MONEY, SepRev MONEY

,OctRev MONEY, NovRev MONEY, DecRev MONEY

,JanExp MONEY, FebExp MONEY, MarExp MONEY

,AprExp MONEY, MayExp MONEY, JunExp MONEY

,JulExp MONEY, AugExp MONEY, SepExp MONEY

,OctExp MONEY, NovExp MONEY, DecExp MONEY

)

SET NOCOUNT ON

-- Load Sample data

;WITH Tally(n) AS (

SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)

INSERT INTO #ProfitLoss

SELECT ProductLine + RIGHT('000000' + CAST(n AS VARCHAR(7)), 7)

, JanRev, FebRev, MarRev, AprRev, MayRev, JunRev, JulRev, AugRev, SepRev, OctRev, NovRev, DecRev

, JanExp, FebExp, MarExp, AprExp, MayExp, JunExp, JulExp, AugExp, SepExp, OctExp, NovExp, DecExp

FROM (

SELECT 'Automotive'

,ABS(CHECKSUM(NEWID())) % 50000 + 10000

,ABS(CHECKSUM(NEWID())) % 30000 + 5000

UNION ALL SELECT 'Parts'

,ABS(CHECKSUM(NEWID())) % 50000 + 10000

,ABS(CHECKSUM(NEWID())) % 30000 + 5000

UNION ALL SELECT 'Leasing'

,ABS(CHECKSUM(NEWID())) % 50000 + 10000

,ABS(CHECKSUM(NEWID())) % 30000 + 5000

) a (ProductLine, JanRev, FebRev, MarRev, AprRev, MayRev, JunRev, JulRev, AugRev, SepRev, OctRev, NovRev, DecRev

, JanExp, FebExp, MarExp, AprExp, MayExp, JunExp, JulExp, AugExp, SepExp, OctExp, NovExp, DecExp)

CROSS APPLY Tally

Next, to be fair, we'll take your version and comment out the ORDER BY because I don't need it for CROSS APPLY VALUES (the natural row ordering works quite well for this example). I also made yours reference the actual columns instead of $0 (BTW - very nice of you to set it up that way).

PRINT '---- UNPIVOT 3000 rows --> 27,000 rows'

SET STATISTICS TIME ON

;WITH [CTE_DETAIL]([ID],[ProductLine]

,[JanRev],[JanExp],[FebRev],[FebExp],[MarRev],[MarExp]

,[AprRev],[AprExp],[MayRev],[MayExp],[JunRev],[JunExp]

,[JulRev],[JulExp],[AugRev],[AugExp],[SepRev],[SepExp]

,[OctRev],[OctExp],[NovRev],[NovExp],[DecRev],[DecExp])

AS

(

SELECT[ID],

[ProductLine],

--$0 AS

[JanRev],

--$0 AS

[JanExp],

--$0 AS

[FebRev],

--$0 AS

[FebExp],

--$0 AS

[MarRev],

--$0 AS

[MarExp],

--$0 AS

[AprRev],

--$0 AS

[AprExp],

--$0 AS

[MayRev],

--$0 AS

[MayExp],

--$0 AS

[JunRev],

--$0 AS

[JunExp],

--$0 AS

[JulRev],

--$0 AS

[JulExp],

--$0 AS

[AugRev],

--$0 AS

[AugExp],

--$0 AS

[SepRev],

--$0 AS

[SepExp],

--$0 AS

[OctRev],

--$0 AS

[OctExp],

--$0 AS

[NovRev],

--$0 AS

[NovExp]

,

--$0 AS

[DecRev],

--$0 AS

[DecExp]

FROM#ProfitLoss

),

[CTE_DETAIL_UNPIVOTED]([ID],[ProductLine],[sMONTH],[iMONTH],[sACCT],[AMOUNT])

AS

(

SELECT[ID],

[ProductLine],

LEFT([PERIOD_ITEM], 3) AS [sMONTH],

MONTH(LEFT([PERIOD_ITEM], 3) + '1, 2000') AS [iMONTH],

RIGHT([PERIOD_ITEM], LEN([PERIOD_ITEM]) - 3) AS [sACCT],

[AMOUNT]

FROM(

SELECT[ID],

[ProductLine],

[JanRev],

[JanExp],

[JanRev]-[JanExp] AS [JanMargin],

[FebRev],

[FebExp],

[FebRev]-[FebExp] AS [FebMargin],

[MarRev],

[MarExp],

[MarRev]-[MarExp] AS [MarMargin],

[AprRev],

[AprExp],

[AprRev]-[AprExp] AS [AprMargin],

[MayRev],

[MayExp],

[MayRev]-[MayExp] AS [MayMargin],

[JunRev],

[JunExp],

[JunRev]-[JunExp] AS [JunMargin],

[JulRev],

[JulExp],

[JulRev]-[JulExp] AS [JulMargin],

[AugRev],

[AugExp],

[AugRev]-[AugExp] AS [AugMargin],

[SepRev],

[SepExp],

[SepRev]-[SepExp] AS [SepMargin],

[OctRev],

[OctExp],

[OctRev]-[OctExp] AS [OctMargin],

[NovRev],

[NovExp],

[NovRev]-[NovExp] AS [NovMargin],

[DecRev],

[DecExp],

[DecRev]-[DecExp] AS [DecMargin]

FROM[CTE_DETAIL]

) AS [PIVOTED] UNPIVOT

(

[AMOUNT] FOR [PERIOD_ITEM] IN

(

[JanRev],

[JanExp],

[JanMargin],

[FebRev],

[FebExp],

[FebMargin],

[MarRev],

[MarExp],

[MarMargin],

[AprRev],

[AprExp],

[AprMargin],

[MayRev],

[MayExp],

[MayMargin],

[JunRev],

[JunExp],

[JunMargin],

[JulRev],

[JulExp],

[JulMargin],

[AugRev],

[AugExp],

[AugMargin],

[SepRev],

[SepExp],

[SepMargin],

[OctRev],

[OctExp],

[OctMargin],

[NovExp],

[NovRev],

[NovMargin],

[DecRev],

[DecExp],

[DecMargin]

)

) AS [UNPIVIOTED]

),

[CTE_DETAIL_CALC]

(

[ID],

[ProductLine],

[sMONTH],

[sACCT],

[AMOUNT],

[ $ O/(U) Prev Mo],

[ % O/(U) Prev Mo]

)

AS

(

SELECTA.[ID],

A.[ProductLine],

A.[sMONTH],

A.[sACCT],

A.[AMOUNT],

COALESCE(A.[AMOUNT] - B.[AMOUNT], $0) AS [ $ O/(U) Prev Mo],

ROUND(100*COALESCE((A.[AMOUNT] - B.[AMOUNT])/NULLIF(B.[AMOUNT],$0),$0), 1) AS [ % O/(U) Prev Mo]

FROM[CTE_DETAIL_UNPIVOTED] A LEFT JOIN

[CTE_DETAIL_UNPIVOTED] B

ONA.[ID] = B.[ID]

ANDA.[sACCT] = B.[sACCT]

ANDA.[iMONTH] = B.[iMONTH] + 1

)

SELECT[ID],

[ProductLine],

[LineItem],

MAX([Jan]) AS [Jan],

MAX([Feb]) AS [Feb],

MAX([Mar]) AS [Mar],

MAX([Apr]) AS [Apr],

MAX([May]) AS [May],

MAX([Jun]) AS [Jun],

MAX([Jul]) AS [Jul],

MAX([Aug]) AS [Aug],

MAX([Sep]) AS [Sep],

MAX([Oct]) AS [Oct],

MAX([Nov]) AS [Nov],

MAX([Dec]) AS [Dec]

FROM(

SELECT[ID],

[ProductLine],

[sACCT],

CASE [sACCT]

WHEN 'Rev' THEN 'Revenues'

WHEN 'Exp' THEN 'Expenses'

WHEN 'Margin' THEN 'Margin'

ELSE 'UNKNOWN'

END AS [LineItem],

[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

FROM(SELECT[ID], [ProductLine], [sMONTH], [sACCT], [AMOUNT], [ $ O/(U) Prev Mo], [ % O/(U) Prev Mo]FROM [CTE_DETAIL_CALC]) AS [UNPIVOTED] PIVOT

(MAX([AMOUNT]) FOR [sMONTH] IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS [PIVOTED_AMOUNT]

UNION

SELECT[ID],

[ProductLine],

[sACCT],

' $ O/(U) Prev Mo' AS [LineItem],

[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

FROM(SELECT[ID], [ProductLine], [sMONTH], [sACCT], [AMOUNT], [ $ O/(U) Prev Mo], [ % O/(U) Prev Mo]FROM [CTE_DETAIL_CALC]) AS [UNPIVOTED] PIVOT

(MAX([ $ O/(U) Prev Mo]) FOR [sMONTH] IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS [PIVOTED_AMOUNT]

UNION

SELECT[ID],

[ProductLine],

[sACCT],

' % O/(U) Prev Mo' AS [LineItem],

[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

FROM(SELECT[ID], [ProductLine], [sMONTH], [sACCT], [AMOUNT], [ $ O/(U) Prev Mo], [ % O/(U) Prev Mo]FROM [CTE_DETAIL_CALC]) AS [UNPIVOTED] PIVOT

(MAX([ % O/(U) Prev Mo]) FOR [sMONTH] IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS [PIVOTED_AMOUNT]

) A

GROUP BY [ID],[ProductLine],[LineItem],[sACCT]

--ORDER BY

--[ID],

--[ProductLine],

--CASE [sACCT]

--WHEN 'Rev' THEN 10

--WHEN 'Exp' THEN 20

--WHEN 'Mar' THEN 30

--ELSE 1000

--END,

--CASE [LineItem]

--WHEN 'Revenues' THEN 10

--WHEN 'Expenses' THEN 10

--WHEN 'Margin' THEN 10

--WHEN ' $ O/(U) Prev Mo' THEN 20

--WHEN ' % O/(U) Prev Mo' THEN 30

--ELSE 1000

--END

SET STATISTICS TIME OFF

The above takes up about 260 lines in my SSMS session by the way, while the following takes up about 75 lines.

Next, the CROSS APPLY VALUES version that handles 12 months, which I've also modified to avoid any possible divide by zero errors that could occur.

PRINT '---- CROSS APPLY VALUES 3000 rows --> 27,000 rows'

SET STATISTICS TIME ON

SELECT ID, ProductLine, LineItem, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

FROM #ProfitLoss

CROSS APPLY (SELECT JanRev-JanExp, FebRev-FebExp, MarRev-MarExp

,AprRev-AprExp, MayRev-MayExp, JunRev-JunExp

,JulRev-JulExp, AugRev-AugExp, SepRev-SepExp

,OctRev-OctExp, NovRev-NovExp, DecRev-DecExp

) y(JanMargin, FebMargin, MarMargin

,AprMargin, MayMargin, JunMargin

,JulMargin, AugMargin, SepMargin

,OctMargin, NovMargin, DecMargin

)

CROSS APPLY (

VALUES ('Revenue', JanRev, FebRev, MarRev, AprRev, MayRev, JunRev

, JulRev, AugRev, SepRev, OctRev, NovRev, DecRev)

,(' $ O/(U) Prev Mo', NULL, FebRev-JanRev, MarRev-FebRev

, AprRev-MarRev, MayRev-AprRev, JunRev-MayRev, JulRev-JunRev, AugRev-JulRev, SepRev-AugRev

, OctRev-SepRev, NovRev-OctRev, DecRev-NovRev)

,(' % O/(U) Prev Mo', NULL, ROUND(100*(FebRev-JanRev)/CASE WHEN JanRev <> 0 THEN JanRev ELSE 1 END, 1)

, ROUND(100*(MarRev-FebRev)/CASE WHEN FebRev <> 0 THEN FebRev ELSE 1 END, 1)

, ROUND(100*(AprRev-MarRev)/CASE WHEN MarRev <> 0 THEN MarRev ELSE 1 END, 1)

, ROUND(100*(MayRev-AprRev)/CASE WHEN AprRev <> 0 THEN AprRev ELSE 1 END, 1)

, ROUND(100*(JunRev-MayRev)/CASE WHEN MayRev <> 0 THEN MayRev ELSE 1 END, 1)

, ROUND(100*(JulRev-JunRev)/CASE WHEN JunRev <> 0 THEN JunRev ELSE 1 END, 1)

, ROUND(100*(AugRev-JulRev)/CASE WHEN JulRev <> 0 THEN JulRev ELSE 1 END, 1)

, ROUND(100*(SepRev-AugRev)/CASE WHEN AugRev <> 0 THEN AugRev ELSE 1 END, 1)

, ROUND(100*(OctRev-SepRev)/CASE WHEN SepRev <> 0 THEN SepRev ELSE 1 END, 1)

, ROUND(100*(NovRev-OctRev)/CASE WHEN NovRev <> 0 THEN NovRev ELSE 1 END, 1)

, ROUND(100*(DecRev-NovRev)/CASE WHEN DecRev <> 0 THEN DecRev ELSE 1 END, 1)

)

,('Expenses', JanExp, FebExp, MarExp, AprExp, MayExp, JunExp

, JulExp, AugExp, SepExp, OctExp, NovExp, DecExp)

,(' $ O/(U) Prev Mo', NULL, FebExp-JanExp, MarExp-FebExp

, AprExp-MarExp, MayExp-AprExp, JunExp-MayExp, JulExp-JunExp, AugExp-JulExp, SepExp-AugExp

, OctExp-SepExp, NovExp-OctExp, DecExp-NovExp)

,(' % O/(U) Prev Mo', NULL, ROUND(100*(FebExp-JanExp)/CASE WHEN JanExp <> 0 THEN JanExp ELSE 1 END, 1)

, ROUND(100*(MarExp-FebExp)/CASE WHEN FebExp <> 0 THEN FebExp ELSE 1 END, 1)

, ROUND(100*(AprExp-MarExp)/CASE WHEN MarExp <> 0 THEN MarExp ELSE 1 END, 1)

, ROUND(100*(MayExp-AprExp)/CASE WHEN AprExp <> 0 THEN AprExp ELSE 1 END, 1)

, ROUND(100*(JunExp-MayExp)/CASE WHEN MayExp <> 0 THEN MayExp ELSE 1 END, 1)

, ROUND(100*(JulExp-JunExp)/CASE WHEN JunExp <> 0 THEN JunExp ELSE 1 END, 1)

, ROUND(100*(AugExp-JulExp)/CASE WHEN JulExp <> 0 THEN JulExp ELSE 1 END, 1)

, ROUND(100*(SepExp-AugExp)/CASE WHEN AugExp <> 0 THEN AugExp ELSE 1 END, 1)

, ROUND(100*(OctExp-SepExp)/CASE WHEN SepExp <> 0 THEN SepExp ELSE 1 END, 1)

, ROUND(100*(NovExp-OctExp)/CASE WHEN OctExp <> 0 THEN OctExp ELSE 1 END, 1)

, ROUND(100*(DecExp-NovExp)/CASE WHEN NovExp <> 0 THEN NovExp ELSE 1 END, 1))

,('Margin', JanMargin, FebMargin, MarMargin, AprMargin, MayMargin, JunMargin

, JulMargin, AugMargin, SepMargin, OctMargin, NovMargin, DecMargin)

,(' $ O/(U) Prev Mo', NULL, FebMargin-JanMargin, MarMargin-FebMargin

, AprMargin-MarMargin, MayMargin-AprMargin, JunMargin-MayMargin, JulMargin-JunMargin

, AugMargin-JulMargin, SepMargin-AugMargin, OctMargin-SepMargin, NovMargin-OctMargin

, DecMargin-NovMargin)

,(' % O/(U) Prev Mo', NULL, ROUND(100*(FebMargin-JanMargin)/CASE WHEN JanMargin <> 0 THEN JanMargin ELSE 1 END, 1)

, ROUND(100*(MarMargin-FebMargin)/CASE WHEN FebMargin <> 0 THEN FebMargin ELSE 1 END, 1)

, ROUND(100*(AprMargin-MarMargin)/CASE WHEN MarMargin <> 0 THEN MarMargin ELSE 1 END, 1)

, ROUND(100*(MayMargin-AprMargin)/CASE WHEN AprMargin <> 0 THEN AprMargin ELSE 1 END, 1)

, ROUND(100*(JunMargin-MayMargin)/CASE WHEN MayMargin <> 0 THEN MayMargin ELSE 1 END, 1)

, ROUND(100*(JulMargin-JunMargin)/CASE WHEN JunMargin <> 0 THEN JunMargin ELSE 1 END, 1)

, ROUND(100*(AugMargin-JulMargin)/CASE WHEN JulMargin <> 0 THEN JulMargin ELSE 1 END, 1)

, ROUND(100*(SepMargin-AugMargin)/CASE WHEN AugMargin <> 0 THEN AugMargin ELSE 1 END, 1)

, ROUND(100*(OctMargin-SepMargin)/CASE WHEN SepMargin <> 0 THEN SepMargin ELSE 1 END, 1)

, ROUND(100*(NovMargin-OctMargin)/CASE WHEN OctMargin <> 0 THEN OctMargin ELSE 1 END, 1)

, ROUND(100*(DecMargin-NovMargin)/CASE WHEN NovMargin <> 0 THEN NovMargin ELSE 1 END, 1))

) x(LineItem, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)

SET STATISTICS TIME OFF

DROP TABLE #ProfitLoss

Neither is a piece of code to be particularly proud of from a maintainability perspective, however we'll let the readers decide which they would rather manage. As that is strictly subjective, we'll now take a look at something a little less so, i.e., actual performance.

After running all of the code above about 5 times to let SQL cache an execution plan, we captured these results.

---- UNPIVOT 3000 rows --> 27,000 rows

Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:

CPU time = 17907 ms, elapsed time = 7043 ms.

---- CROSS APPLY VALUES 3000 rows --> 27,000 rows

SQL Server Execution Times:

CPU time = 109 ms, elapsed time = 479 ms.

That strikes me as a pretty hefty price tag to pay in performance for maintainability! Please let me know if I've included something in your code that was not your intention.

As to your last comment, about users then wanting to see year over year, quarter over quarter, etc., I wouldn't put it past those pesky bean counters to suggest such a thing. I would argue that adding another 24 columns to represent a second year (not sure if that's what you meant or not) is probably pretty sick and is certainly not a good database design. Neither is the 24 month buckets in this example for that matter.

But please allow me the flexibility to come up with an example for my article, even though contrived it may be! Note that I have seen GLs built with 12 monthly buckets across, so that is not too far-fetched.

Most of all though, thanks for your contribution and opinions. As I said before, there will probably be differing opinions on much of this, and that is always valued for the perspective it adds.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Mike McIver SSC-Addicted Points: 493 More actions · Answer 7