Development : SQL 2005

Pivot Rolling Periods : Dynamic, But Not!

Kevin Cross — Fri, 18 Sep 2009 03:53:00 GMT

In the Article, Pivot Rolling Periods Without Using Dynamic T-SQL (experts-exchange.com/viewArticle.jsp?aid=654), I explored the pivot of moving date based reports without the use of dynamic T-SQL, but still allow for re-use month after month without re-writing of code.

Here is another introductory tidbit to SQL Server 2005's PIVOT (technet.microsoft.com/en-us/library/ms177410.aspx). We will embrace the limitations of the PIVOT keyword requiring hard coded values / column names, but still provide nice reports that can be used in views or quick queries without getting into multi-line dynamic code.

To play along, you can download and run the following DDL and data insert SQL script:

(Please visit the site to view this media)

Okay, now we can play. The following starts out by getting data greater or equal to last January 1st at midnight "dateadd(year, datediff(year, 0, getdate())-1, 0)". Then we employ a date function to make our datetime value non-specific to year (i.e., month(bk_date) will yield 1-12). For this report, we want to have a year over year analysis of data by month so we also use year(bk_date) to get our grouped column heading which doesn't affect us in the PIVOT since it will remain as data.

;with bks( byear, bmonth, amount )
as
(
select year(bk_date), month(bk_date), bk_amount
from bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate())-1, 0)
)
select byear as "Sales Year",
coalesce("1", 0.00) as January,
coalesce("2", 0.00) as February,
coalesce("3", 0.00) as March,
coalesce("4", 0.00) as April,
coalesce("5", 0.00) as May,
coalesce("6", 0.00) as June,
coalesce("7", 0.00) as July,
coalesce("8", 0.00) as August,
coalesce("9", 0.00) as September,
coalesce("10", 0.00) as October,
coalesce("11", 0.00) as November,
coalesce("12", 0.00) as December
from bks
pivot( sum(amount) for bmonth 
       in ("1","2","3","4","5","6","7","8","9","10","11","12") ) pvt
order by "Sales Year" desc

Results will look similar to below:

The above uses the month number, since it has to be typed multiple times as use explicit select list to use functions like coalesce(). This is more important when doing advanced pivoting like with multiple columns (experts-exchange.com/viewArticle.jsp?aid=1537); however, since we have a simple case here where null may be okay as it quickly answers the question of a missing data point versus a very poor $0.00 booking month, we can use a much simplified code:

with bks("Sales Year", bmonthname, amount)
as
(
select year(bk_date), datename(month, bk_date), bk_amount
from bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate())-1, 0)
)
select *
from bks
pivot( sum(amount) for bmonthname
       in (January, February, March, April, May, June,
           July, August, September, October, November, December) ) pvt
order by "Sales Year" desc

This version looks similar to this:

As you will see, we haven't lost any value in the actual data and gained less code to maintain and a clear cut distinction between 0's and no data as already stated. We utilize the datename() function here to strip out the year and day as we did before, but instead of just a number we return full month. For consistency, you can use left( datename( month, bk_date ), 3 ) and you will get 'Jan', 'Feb', 'Mar', etc. It is normally not a good practice to use wilcard (*) for selecting records, but since we explicitly defined the columns we wanted in the CTE, 'select *' is very safe here as we are still protected from changes in the underlying table structure as we are selecting all from the CTE and not the base table.

Notice, we still following the same syntax on the pivot, just changed the actual values in play.

pivot( aggregate_function( column_to_aggregate ) for column_to_pivot
       in ( pivot_value1 [, pivot_value2 [, ... pivot_valuen]] )
) pivot_alias

Hopefully that was helpful!

This was my first entry, so hopefully you liked it. Will work on adding more to it if folks are interested as I use these kinds of queries frequently for things like item inventory turns over or dependent / independent usage over the past 12 months, so have played with it a bit.

Happy coding!

-- Kevin

Concatenating Results through SELECT statements

TimChapman — Fri, 20 Mar 2009 20:53:00 GMT

In this SQL tip I am going to show you a neat little trick which involves using a simple SELECT statement to concatenate row values into a single variable value.

First, I'll create a temp table named #Results for which I'll store FirstName and LastName data.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    FirstName varchar(25), 
    LastName varchar(25)
)

GO

This script adds some names to the temp table I just created.

INSERT INTO #Results(FirstName, LastName)
SELECT 'Tim', 'Chapman' 
UNION ALL
SELECT 'Brandon', 'Galderisi' 
UNION ALL
SELECT 'Aaron','Akin' 
UNION ALL
SELECT 'Mark', 'Wills' 
UNION ALL
SELECT 'Angel','Eyes' 
UNION ALL
SELECT 'Daniel','Wilson'

GO

The following script does the meat of the work. I declare a variable to hold the name values and use the variable in my SELECT statement. The SELECT statement essentially takes every value from the #Results table and appends it to the @FullNames variable.

DECLARE @FullNames VARCHAR(200)

SELECT
    @FullNames = ISNULL(@FullNames,'') + LastName + ', ' + FirstName + ' '
FROM #Results

PRINT @FullNames

The result of printing the value in the variable is below.

Chapman, Tim Galderisi, Brandon Akin, Aaron Wills, Mark Eyes, Angel Wilson, Daniel

If I want to add a line feed to the varaible for formatting purposes, it is easy enough to do by adding the CHAR(10) character to the variable string. This method will insert a new line feed between each value.

DECLARE @FullNames VARCHAR(100)

SELECT
    @FullNames = ISNULL(@FullNames,'') + LastName + ', ' + FirstName + ' ' + CHAR(10)
FROM #Results

PRINT @FullNames

Printing the contents of the @FullNames variable leaves me with the following output.

Chapman, Tim 
Galderisi, Brandon 
Akin, Aaron 
Wills, Mark 
Eyes, Angel 
Wilson, Daniel

Nested Common Table Expressions

Aaron Akin — Thu, 19 Mar 2009 02:03:00 GMT

If you've looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can't define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner. Here is an example of a simple CTE using the AdventureWorks database that will return a list of orders.

USE AdventureWorks

;WITH CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT * FROM CTE_Orders

OrderID     OrderDate
----------- ----------
1           2001-05-17             
2           2001-05-17             
3           2001-05-17             
4           2001-05-17             
5           2001-05-31             
...

What isn't very clear is the use of multiple CTE definitions in one statement. Here is an example of multiple CTEs used in one statement. Each CTE in the statement has its own definition, and the final SELECT statement is able to query both CTEs. This will return a list of dates that orders were made along with the number of products ordered and the total amount of those products for each date.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt
---------- ------------- -------------
2001-05-17 563           9491.518
2001-05-31 2765          94404.303
2002-01-14 718           39874.7685
2002-01-15 11931         259365.2145
2002-02-08 2768          50781.36
...

If you take this concept of having multiple CTE definitions in one statement, you can then apply it to a statement with nested CTEs. I'm using the following statement to return the same result set as in the previous example, but this time I want to know the number of orders for each date that had more than 100 products ordered.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate, IsLargeOrder) AS
(
  SELECT PurchaseOrderID, OrderDate
       , (
         SELECT CASE
                  WHEN SUM(OrderDetailQty) >= 100 THEN 1
                  ELSE 0
                END
           FROM CTE_OrdersDetail d
           WHERE h.PurchaseOrderID = d.OrderID
         )
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty
   , SUM(od.OrderDetailAmt) AS TotalOrderAmt, SUM(o.IsLargeOrder) AS NumLargeOrders
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate

OrderDate  TotalOrderQty TotalOrderAmt         NumLargeOrders
---------- ------------- --------------------- --------------
2001-05-17 563           9491.518              1
2001-05-31 2765          94404.303             5
2002-01-14 718           39874.7685            1
2002-01-15 11931         259365.2145           26
2002-02-08 2768          50781.36              5
...

As you can see from this example, the CTE_OrdersDetail expression is being referenced within the definition of the CTE_Orders expression, giving you the functionality of nested CTEs. In order for a statement like this to work properly, you must declare each CTE definition before it is used in another CTE. This means that since CTE_OrdersDetail is being reference within CTE_Orders, I must declare CTE_OrdersDetail first. Otherwise, if the two definitions were switched, SQL would not be able to find the CTE_OrdersDetail object when it goes to verify the CTE_Orders expression.

Aaron
http://aaronakinsql.wordpress.com