## Pivot dynamic sql and calculated columns

## Pivot dynamic sql and calculated columns

(OP)

I have this static version of a pivot

which gives this data

and then when it is dynamic it is those month-year columns that change and are set dynamically. Now, my challenge is that I need to supply two percentage calculations with the first being the % change between the second to last column and the last column (217% and 95% respectively (b-a)/a) and then I need to know the breakdown of the percentage XX and YY of the total (in this case 46% and 54% respectively). What I cannot see how to do is how do I programmatically choose the last column and perform calculations on either that column and the one before it or between the rows in that column?

Thank you for any help you can give me!

Willie

#### CODE

SELECT SBType,[2016-06],[2016-08],[2017-01] FROM ( SELECT [MonthYear] ,Amount ,SBType FROM [dbo].[vw_history] WHERE SN=36521487 AND Amount>0 AND (Direction='Incoming' OR IsIncoming=1) AND IsReturn=0 )src PIVOT ( SUM(Amount) for MonthYear in ([2016-06],[2016-08],[2017-01]) ) as pvt

which gives this data

#### CODE

SBType 2016-06 2016-08 2017-01 XX 91199725.1118 96441374.732 305296408.57 YY 147496746.94 186075209.717 362227931.77

and then when it is dynamic it is those month-year columns that change and are set dynamically. Now, my challenge is that I need to supply two percentage calculations with the first being the % change between the second to last column and the last column (217% and 95% respectively (b-a)/a) and then I need to know the breakdown of the percentage XX and YY of the total (in this case 46% and 54% respectively). What I cannot see how to do is how do I programmatically choose the last column and perform calculations on either that column and the one before it or between the rows in that column?

Thank you for any help you can give me!

Willie

## RE: Pivot dynamic sql and calculated columns

It that way it is both static and dynamic.

Simi

## RE: Pivot dynamic sql and calculated columns

Although it often scares beginners a CTE definition will give you the chance to formulate such a more complex query as two simpler with a simple name (the cte name) being a placeholder for the inner subquery, the query you already have. CTEs scare people off, because they have the half knowledge this is something about recursion. Only if the CTE query references itself. You can also have simple CTEs just giving better readability than a complex query.

First to illustrate that:

## CODE

The first query is not that convoluted, that it suggests a separate cte notation, but in the end it's just another way of giving a subquery a name. With the benefit of possible recursion and other properties you may set at the end of the cte parenthesis.

For your case, 1st degree (the change percentages

## CODE

Notice, the main part of the asnwer here is just the lower query Select *, ([2017-01]-[2016-08])/[2016-08]*100 as change from pivoted. You could also have gotten there yourself without knowing CTE and just knowing the possibility of subqueries, if you put your whole query into brackets instead of the cte name 'pivoted'.

Know to get percenteges of the rows in relation to a total is a third query. For that you can simply define two CTEs and do the last one as final query:

## CODE

Result:

Even without knowing CTE you could have done that with subqueries, simple brackets around inner queries you give an alias name. The outer queries are simply your percentage calculations, then.

Bye, Olaf.

## RE: Pivot dynamic sql and calculated columns

PERCENTILE_CONT - https://blog.sqlauthority.com/2011/11/20/sql-serve...

PERCENTILE_DISC - https://appliedsql.net/2014/02/16/percentile_disc-...

Bye, Olaf.

## RE: Pivot dynamic sql and calculated columns

## CODE