CTE Example with Pivot operator

Question

CTE Example with Pivot operator

KeithDB

SSC Enthusiast

Points: 180
More actions
April 12, 2019 at 2:48 pm

#3159097

Comments posted to this topic are about the item CTE Example with Pivot operator

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

My only complaint about your code is the semicolon preceding the WITH for your CTE. When creating a CTE it is required that the PREVIOUS statement be terminated with a semicolon, not the CTE begin with a CTE. It is obvious that people doing this leads others not familiar with writing CTEs to think that the semicolon is required before the WITH when declaring a CTE.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Being picky, I do have a few other minor comments about the code:

The created table has no schema name
Literal dates are not in ISO format ('YYYYMMDD')
Inconsistent use of square brackets (look at order_dt, for example)
Coalesce() with two arguments is equivalent to IsNull() ... might as well save yourself some typing

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 3

wrote:

Coalesce() with two arguments is equivalent to IsNull() ... might as well save yourself some typing

Actually, Phil, I feel I need to correct you on that one. ISNULL and COALESCE do behave differently. COALESCE is a shortcut function for a CASE expression and therefore uses Data Type Precedence to determine the return value. ISNULL, however, returns the data type of the first parameter.

I certainly agree on the dates though. Most of those would fail to convert to a date on my instance, or would provide the wrong value.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

If we're going to be picky, I have some other things to note:

The keywords are not case consistent, some are written in upper case and some in lower case.
The format is not consistent either, sometimes the FROM will be on the same line and sometimes on the next one.
There are multiple columns in the same line which reduces readability.

Those were just formatting issues, but my main problems are:

The orders table is called twice when we only need to call it once.
The use of pivot instead of using a cross tabs query.

My version would turn out like this:

WITH preAggregate AS(
    SELECT  YEAR(order_dt) as [Year],
        MONTH( [order_dt]) AS [month],
        SUM( [amount]) AS [amount]
    FROM orders
    GROUP BY YEAR(order_dt),
             MONTH( [order_dt])
)
SELECT  [Year],
    SUM( [amount]) AS [Annual Total],
    SUM( CASE WHEN [month] =  1 THEN [amount] ELSE 0 END) AS [Jan],
    SUM( CASE WHEN [month] =  2 THEN [amount] ELSE 0 END) AS [Feb],
    SUM( CASE WHEN [month] =  3 THEN [amount] ELSE 0 END) AS [Mar],
    SUM( CASE WHEN [month] =  4 THEN [amount] ELSE 0 END) AS [Apr],
    SUM( CASE WHEN [month] =  5 THEN [amount] ELSE 0 END) AS [May],
    SUM( CASE WHEN [month] =  6 THEN [amount] ELSE 0 END) AS [Jun],
    SUM( CASE WHEN [month] =  7 THEN [amount] ELSE 0 END) AS [Jul],
    SUM( CASE WHEN [month] =  8 THEN [amount] ELSE 0 END) AS [Aug],
    SUM( CASE WHEN [month] =  9 THEN [amount] ELSE 0 END) AS [Sep],
    SUM( CASE WHEN [month] = 10 THEN [amount] ELSE 0 END) AS [Oct],
    SUM( CASE WHEN [month] = 11 THEN [amount] ELSE 0 END) AS [Nov],
    SUM( CASE WHEN [month] = 12 THEN [amount] ELSE 0 END) AS [Dec]
FROM preAggregate
GROUP BY [Year];

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

Thanks Thom, quite right! I should have added the text "in this case".

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel