Wednesday, September 30, 2009

Listening to map events in Google Maps API v3 OverlayView

In my previous post, I showed how to create a simple Label overlay. In that example, the draw method was only called when the position of the overlay relative to the map needed to be recalculated or when the text for the label had changed.

But what if the look/content of the overlay you want to create depends on the map center or the map bounds. Currently, OverlayView.draw is not called if the map is dragged for example.

I've created below a 'Center' overlay to demonstrate how to listen to map events in the overlay.

first, in center.js I have:

// Define the overlay, derived from google.maps.OverlayView
function Center(opt_options) {
// Initialization
this.setValues(opt_options);

// Center specific
var div1 = document.createElement('div');
div1.style.cssText = 'position: absolute; left: -20px; top: -20px; ' +
'border-right: 1px solid blue; border-bottom: 1px solid blue; width: 19px; height: 19px';
var div2 = document.createElement('div');
div2.style.cssText = 'position: absolute; left: 1px; top: -20px; ' +
'border-left: 1px solid blue; border-bottom: 1px solid blue; width: 19px; height: 19px';
var div3 = document.createElement('div');
div3.style.cssText = 'position: absolute; left: -20px; top: 1px; ' +
'border-right: 1px solid blue; border-top: 1px solid blue; width: 19px; height: 19px';
var div4 = document.createElement('div');
div4.style.cssText = 'position: absolute; left: 1px; top: 1px; ' +
'border-left: 1px solid blue; border-top: 1px solid blue; width: 19px; height: 19px';

var div = this.div_ = document.createElement('div');
div.appendChild(div1);
div.appendChild(div2);
div.appendChild(div3);
div.appendChild(div4);
div.style.cssText = 'position: absolute; display: none';
};
Center.prototype = new google.maps.OverlayView;

// Implement onAdd
Center.prototype.onAdd = function() {
var pane = this.getPanes().overlayLayer;
pane.appendChild(this.div_);

// Ensures the center is redrawn if the map center changes
var me = this;
this.listeners_ = [
google.maps.event.addListener(this.getMap(), 'center_changed',
function() { me.draw(); }),
];
};

// Implement onRemove
Center.prototype.onRemove = function() {
this.div_.parentNode.removeChild(this.div_);

// Label is removed from the map, stop updating its position/text.
for (var i = 0, I = this.listeners_.length; i < I; ++i) {
maps.google.event.removeListener(this.listeners_[i]);
}
};

// Implement draw
Center.prototype.draw = function() {
var projection = this.getProjection();
var position = projection.fromLatLngToDivPixel(this.getMap().getCenter());

var div = this.div_;
div.style.left = position.x + 'px';
div.style.top = position.y + 'px';
div.style.display = 'block';
};

and in the map initialization I just added:

<script type="text/javascript" src="center.js"></script>

and

var center = new Center({
map: map
});

Adding the Center object to the map creates a cross at the center of the map. When the map is dragged or zoomed, the overlay is redrawn, allowing it to stay centered.

The result looks like this:

Tuesday, September 29, 2009

Label overlay example for Google Maps API v3

Here's a simple example of creating a custom overlay class for Google Maps API v3. This Label overlay can either be used on its own, or bound to a marker.

First, create the Label class and place it in a label.js file.

// Define the overlay, derived from google.maps.OverlayView
function Label(opt_options) {
// Initialization
this.setValues(opt_options);

// Label specific
var span = this.span_ = document.createElement('span');
span.style.cssText = 'position: relative; left: -50%; top: -8px; ' +
'white-space: nowrap; border: 1px solid blue; ' +
'padding: 2px; background-color: white';

var div = this.div_ = document.createElement('div');
div.appendChild(span);
div.style.cssText = 'position: absolute; display: none';
};
Label.prototype = new google.maps.OverlayView;

// Implement onAdd
Label.prototype.onAdd = function() {
var pane = this.getPanes().overlayLayer;
pane.appendChild(this.div_);

// Ensures the label is redrawn if the text or position is changed.
var me = this;
this.listeners_ = [
google.maps.event.addListener(this, 'position_changed',
function() { me.draw(); }),
google.maps.event.addListener(this, 'text_changed',
function() { me.draw(); })
];
};

// Implement onRemove
Label.prototype.onRemove = function() {
this.div_.parentNode.removeChild(this.div_);

// Label is removed from the map, stop updating its position/text.
for (var i = 0, I = this.listeners_.length; i < I; ++i) {
google.maps.event.removeListener(this.listeners_[i]);
}
};

// Implement draw
Label.prototype.draw = function() {
var projection = this.getProjection();
var position = projection.fromLatLngToDivPixel(this.get('position'));

var div = this.div_;
div.style.left = position.x + 'px';
div.style.top = position.y + 'px';
div.style.display = 'block';

this.span_.innerHTML = this.get('text').toString();
};

Then use label.js in the map initialization:

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
<title>Label Overlay Example</title>
<script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"></script>
<script type="text/javascript" src="label.js"></script>
<script type="text/javascript">
function initialize() {
var latLng = new google.maps.LatLng(40, -100);

var map = new google.maps.Map(document.getElementById('map_canvas'), {
zoom: 5,
center: latLng,
mapTypeId: google.maps.MapTypeId.ROADMAP
});

var marker = new google.maps.Marker({
position: latLng,
draggable: true,
map: map
});

var label = new Label({
map: map
});
label.bindTo('position', marker, 'position');
label.bindTo('text', marker, 'position');
};
</script>
</head>
<body onload="initialize()">
<div id="map_canvas" style="height: 100%; width: 100%"></div>
</body>
</html>

When run, it looks like this:



Drag the marker around and the text of the label with update with the LatLng of the marker.

Friday, September 11, 2009

Hosting Google Maps in a Microsoft WPF application using XAML

Ever wondered how to implement Google Maps in a WPF application using XAML?

Find out how: http://code.google.com/apis/maps/articles/flashmapinwpf.html

Monday, May 4, 2009

Filtered ObservableCollection

On the project I'm working on at the moment, I have a class that stores items in an ObservableCollection. I needed to be able to expose in the same class different subsets of this collection, keeping them all in sync while allowing to add/remove/update elements in all the collections.

For example, imagine you would like to create a collection of people ObservableCollection<People> but also have collection for Males and Females and pass these collections as parameters to other methods that can manipulate these collections (Add/Remove/Update People in the Males collection) and see the original collection updated.

My first approach was to create an ObservableCollection for each subset and use the CollectionChanged event to maintain all the lists in sync. While it worked, it was very code heavy. So today I wrote a simple FilteredObservableCollection class. It encapsulates an ObservableCollection but only enumerates the items that comply with the filter. It's similar to the CollectionView but it still allows to Add/Remove/Update elements in the collection.

The class is not derived from an ObservableCollection since it needs to be able to attach to an existing collection but it expose all the interfaces and members for the observable collection:

public
class
FilteredObservableCollection<T> : IList<T>, ICollection<T>, IEnumerable<T>, IList, ICollection, IEnumerable, INotifyCollectionChanged, INotifyPropertyChanged
{
    private
ObservableCollection<T> _collection;
    private
Predicate<T> _filter;
    private
event
NotifyCollectionChangedEventHandler _collectionchanged;
    private
event
PropertyChangedEventHandler _propertychanged;

    public FilteredObservableCollection(ObservableCollection<T> collection)
    {
        _filter = null;
        _collection = collection;
        _collection.CollectionChanged += new
NotifyCollectionChangedEventHandler(OnCollectionChanged);
        ((INotifyPropertyChanged)_collection).PropertyChanged += new
PropertyChangedEventHandler(OnPropertyChanged);
    }


 

The two interesting bit in the Filtered collection are the handling on the CollectionChanged event and the enumerator:

The CollectionChanged event received from the encapsulated collection may not necessarily need to be passed by the filtered collection. An item added to or deleted from the main collection, if filtered out, should not generate an event for the filtered collection while a Change to an existing item may cause the item to appear in the list (it now complies with the filter) or disappear from the list (it no longer complies with the filter).

    private
void OnCollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
    {
        if (_collectionchanged != null)
        {
            // Check the NewItems
            List<T> newlist = new
List<T>();
            if (e.NewItems != null)
                foreach (T item in e.NewItems)
                    if (_filter(item) == true)
                        newlist.Add(item);

            // Check the OldItems
            List<T> oldlist = new
List<T>();
            if (e.OldItems != null)
                foreach (T item in e.OldItems)
                    if (_filter(item) == true)
                        oldlist.Add(item);

            // Create the Add/Remove/Replace lists
            List<T> addlist = new
List<T>();
            List<T> removelist = new
List<T>();
            List<T> replacelist = new
List<T>();

            // Fill the Add/Remove/Replace lists
            foreach (T item in newlist)
                if (oldlist.Contains(item))
                    replacelist.Add(item);
                else
                    addlist.Add(item);
            foreach (T item in oldlist)
                if (newlist.Contains(item))
                    continue;
                else
                    removelist.Add(item);

            // Send the corrected event
            switch (e.Action)
            {
                case
NotifyCollectionChangedAction.Add:
                case
NotifyCollectionChangedAction.Move:
                case
NotifyCollectionChangedAction.Remove:
                case
NotifyCollectionChangedAction.Replace:
                    if (addlist.Count > 0)
                        _collectionchanged(this, new
NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Add, addlist));
                    if (replacelist.Count > 0)
                        _collectionchanged(this, new
NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Replace, replacelist));
                    if (removelist.Count > 0)
                        _collectionchanged(this, new
NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Remove, removelist));
                    break;
                case
NotifyCollectionChangedAction.Reset:
                    _collectionchanged(this, new
NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Reset));
                    break;
            }
        }
    }

The enumerator starts from the first compliant element in the collection and each call to Next() makes the cursor move to the next valid filtered item.

    private
class
FilteredEnumerator : IEnumerator<T>, IEnumerator
    {
        private
FilteredObservableCollection<T> _filteredcollection;
        private
IEnumerator<T> _enumerator;

        public FilteredEnumerator(FilteredObservableCollection<T> filteredcollection, IEnumerator<T> enumerator)
        {
            _filteredcollection = filteredcollection;
            _enumerator = enumerator;
        }

        public T Current
        {
            get
            {
                if (_filteredcollection.Filter == null)
                    return _enumerator.Current;
                else
if (_filteredcollection.Filter(_enumerator.Current) == false)
                    throw
new
InvalidOperationException();
                else
                    return _enumerator.Current;
            }
        }

        public
void Dispose()
        {
            _enumerator.Dispose();
        }

        object
IEnumerator.Current
        {
            get { return
this.Current; }
        }

        public
bool MoveNext()
        {
            while (true)
            {
                if (_enumerator.MoveNext() == false)
                    return
false;
                if (_filteredcollection.Filter == null
                    || _filteredcollection.Filter(_enumerator.Current) == true)
                    return
true;
            }
        }

        public
void Reset()
        {
            _enumerator.Reset();
        }
    }
}

It's worked well so far but a lot remains to be done. I need to optimize the code to avoid for example having to calculate the Count at every call. I need to test all the possible collection manipulation and see if the CollectionChanged event is processed correctly. I need to implement the missing Move() method. I need to test multi-threading.

I've made the code available for download as part of a sample project that displays a list of integers and two subsets of multiples of 2 and of 3.

public
class
Data : DependencyObject
{
    public
static
readonly
DependencyProperty FullListProperty =
     DependencyProperty.Register("FullList", typeof(ObservableCollection<int>), typeof(Data));
    public
static
readonly
DependencyProperty List_2Property =
     DependencyProperty.Register("List_2", typeof(FilteredObservableCollection<int>), typeof(Data));
    public
static
readonly
DependencyProperty List_3Property =
     DependencyProperty.Register("List_3", typeof(FilteredObservableCollection<int>), typeof(Data));

    public
ObservableCollection<int> FullList
    {
        get { return (ObservableCollection<int>)GetValue(FullListProperty); }
        set { SetValue(FullListProperty, value); }
    }

    public
FilteredObservableCollection<int> List_2
    {
        get { return (FilteredObservableCollection<int>)GetValue(List_2Property); }
        set { SetValue(List_2Property, value); }
    }

    public
FilteredObservableCollection<int> List_3
    {
        get { return (FilteredObservableCollection<int>)GetValue(List_3Property); }
        set { SetValue(List_3Property, value); }
    }

    public Data()
    {
        FullList = new
ObservableCollection<int>();
        List_2 = new
FilteredObservableCollection<int>(FullList);
        List_3 = new
FilteredObservableCollection<int>(FullList);

        List_2.Filter = new
Predicate<int>(Filter_2);
        List_3.Filter = new
Predicate<int>(Filter_3);
    }

    private
bool Filter_2(int x) { return x % 2 == 0; }
    private
bool Filter_3(int x) { return x % 3 == 0; }
}

I've used a DependencyObject and DependencyProperties but it wasn't necessary to use the FilteredObservableCollection.


Download the project source code here: Download

Attached DependencyProperty

In my previous blog on DependencyProperty, I talked about how to create properties in objects using DependencyProperty and DependencyObject, to have support for Databinding, defaults, expressions, events and validations.
But on some occasions, you want to assign a parent object needs to assign a property to the children objects, regardless of the type of object.

Traditionally, this has been done using base class and inheritance. For example, if you wanted to create a panel control that understands commands, you would need to create a base class 'CmdControl" something like this:

public
partial
class
CmdControl : UserControl
{
public CmdControl()
    {
        InitializeComponent();
    }

    private
string _cmd;

    public
string Cmd
    {
        get
        {
            return _cmd;
        }
        set
        {
            _cmd = value;
        }
    }
}

Then you would create all your controls derived from CmdControl, thus ensuring that each control has a Cmd property:

public
partial
class
MyControl : CmdControl
{
    public MyControl()
    {
        this.Cmd = "Do this";

        InitializeComponent();
    }
}

But this method did not allow the use of existing controls or classes.

With attached properties, a property can be set for each child control but the storage of the value is handled by the parent class.
Let's see how to declare the Grid control and the Cmd attached property:

public
partial
class
MyPanel : System.Windows.Controls.Grid
{
    public MyPanel()
    {
        InitializeComponent();
    }

    public
static
string GetCmd(DependencyObject obj)
    {
        return (string)obj.GetValue(CmdProperty);
    }

    public
static
void SetCmd(DependencyObject obj, string value)
    {
        obj.SetValue(CmdProperty, value);
    }

    public
static
readonly DependencyProperty CmdProperty =
        DependencyProperty.RegisterAttached("Cmd", typeof(string), typeof(MyPanel), new UIPropertyMetadata("Open"));
}

In XAML, the panel is used as per my previous blog:


 

<Window
x:Class="WPF2.Window1"
    xmlns=http://schemas.microsoft.com/winfx/2006/xaml/presentation
    xmlns:x=http://schemas.microsoft.com/winfx/2006/xaml
    xmlns:AppCode="clr-namespace:WPF2"
    Title="WPF2"
Height="263"
Width="239"
    >
    <
AppCode:MyPanel
x:Name="Panel1">
        <
Button
Name="Button1"
Height="23"
Margin="50,0,50,0" >Button1</Button>
        <
Button
Name="Button2"
Height="23"
Margin="50,50,50,0" >Button2</Button>
    </
AppCode:MyPanel>
</
Window>

You can then programmatically assign a command value to any child control:

public
partial
class
Window1 : System.Windows.Window
{
    public Window1()
    {
        InitializeComponent();

        MyPanel.SetCmd(Button1, "Open 1");
        MyPanel.SetCmd(Button2, "Close 1");

        Button1.Click += new
RoutedEventHandler(Button_Click);
        Button2.Click += new
RoutedEventHandler(Button_Click);
    }

    void Button_Click(object sender, RoutedEventArgs e)
    {
        MessageBox.Show("My Command is " + MyPanel.GetCmd(sender as
DependencyObject));
    }
}

Or you can do the same thing in XAML:

<Window
x:Class="WPF2.Window1"
    xmlns=http://schemas.microsoft.com/winfx/2006/xaml/presentation
    xmlns:x=http://schemas.microsoft.com/winfx/2006/xaml
    xmlns:AppCode="clr-namespace:WPF2"
    Title="WPF2"
Height="263"
Width="239"
    >
    <
AppCode:MyPanel
x:Name="Panel1">
        <
Button
AppCode:MyPanel.Cmd="Open 1"
Click="Button_Click"
Height="23"
Margin="50,0,50,0" >Button1</Button>
        <
Button
AppCode:MyPanel.Cmd="Close 1"
Click="Button_Click"
Height="23"
Margin="50,50,50,0" >Button2</Button>
    </
AppCode:MyPanel>
</
Window>

When you launch the application, and you click on either button, you get a message box with the text of the command assigned to that button.

More information on attached properties can be found here.

DependencyProperty and DependencyObject

Up until now, you would create properties in a class using this syntax:

public
class
MyObject
{
    private
string MyPropertyValue;

    public
string MyProperty
    {
        set
        {
            MyPropertyValue = value;
        }
        get
        {
            return MyPropertyValue;
        }
    }
}

This is fine but then you have a lot of code to add to handle additional features such as PropertyChanged event, data binding, default value, validation.

DependencyObject and DependencyProperty provide a simpler way to implement properties and provide directly off the shelf:

  • DataBinding
  • Events
  • Validation
  • Default Value

As well as elements more specific to Windows Foundation and .NET 3.0:

  • Animation
  • Style
  • Attached properties
  • Expressions

So let's see how to use these new classes.
First we create a class similar to the one above:

public
class
Lift : DependencyObject
{
    public
static
DependencyProperty FloorProperty =
        DependencyProperty.Register("Floor", typeof(int), typeof(Lift));

    public
int Floor
    {
        get { return (int)GetValue(FloorProperty); }
        set { SetValue(FloorProperty, value); }
    }
}

I have a class Lift with a property called Floor. Already I can implement many of the WPF feature. The Lift class is ready for DataBinding, the Floor property will accept expressions to be set and can be used as part of another expression.
Let's look at this simple XAML code:

<Window
x:Class="TestWPF1.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:AppCode="clr-namespace:TestWPF1"
    Title="TestWPF1"
Height="300"
Width="300"
    >
    <
Window.Resources>
        <
AppCode:Lift
x:Key="Lift1"
Floor="1" />
    </
Window.Resources>
    <
StackPanel
Orientation="Horizontal">
        <
TextBlock
Text="{Binding Path=Floor}" />
        <
StackPanel.DataContext>
            <
Binding
Source="{StaticResource Lift1}" />
        </
StackPanel.DataContext>
    </
StackPanel>
</
Window>

I created an instance of Lift in XAML and databound it to the StackPanel.

Still I rely on the user to set the Floor value to a correct value. Why not assign a default value:

    public
static
DependencyProperty FloorProperty =
        DependencyProperty.Register("Floor", typeof(int),typeof(Lift),new
PropertyMetadata(0));

By setting the default value in the metadata, I can now create an instance of the Lift class without specifying the value for Floor:

    <AppCode:Lift
x:Key="Lift1" />

Now I'd like to display the kind of goods for sale on the floor of that building. I add a second property to my class called "Goods".

    public
static
DependencyProperty GoodsProperty =
        DependencyProperty.Register("Goods", typeof(string), typeof(Lift));

    public
string Goods
    {
        get { return (string)GetValue(GoodsProperty); }
        set { SetValue(GoodsProperty, value); }
    }

And I can use it directly in XAML:

    <AppCode:Lift
x:Key="Lift1"
Floor="1"
Goods="Perfumes" />
    …
    <
TextBlock
Text="{Binding Path=Floor}" />
    <
TextBlock
Text=": " />
    <
TextBlock
Text="{Binding Path=Goods}" />

But I'd like set the goods value directly in my class according to the floor set. I can do that by handling the PropertyChanged event on the Floor property. I just expand a bit the property definition to get the Changed event:


 

        public
static
DependencyProperty FloorProperty =
            DependencyProperty.Register("Floor", typeof(int),typeof(Lift),new
PropertyMetadata(0,new
PropertyChangedCallback(OnFloorChanged));

        private
static
void OnFloorChanged(DependencyObject obj, DependencyPropertyChangedEventArgs arg)
        {
            Lift l = (Lift)obj;
            switch (l.Floor)
            {
                case 0:
                    l.Goods = "Books";
                    break;
                case 1:
                    l.Goods = "Perfumes";
                    break;
                case 2:
                    l.Goods = "Toys";
                    break;
                case 3:
                    l.Goods = "Kitchenware";
                    break;
            }
        }

So in XAML, it looks like:


 

    <AppCode:Lift
x:Key="Lift1"
Floor="1" />
    …
    <
TextBlock
Text="{Binding Path=Floor}" />
    <
TextBlock
Text=": " />
    <
TextBlock
Text="{Binding Path=Goods}" />

I no longer have to supply the Goods value, it's set when the Floor value is modified.


 

Ok so we've looked at Databinding, Property Changed, Default value, now we need validation. My building doesn't have an infinite number of floors so I'd like to restrict the Floor value to between 0 and 3.


 

I can add validation by handling the Validate event:


 

    public
static
DependencyProperty FloorProperty =
        DependencyProperty.Register(("Floor", typeof(int),typeof(Lift),new
PropertyMetadata(0,new
PropertyChangedCallback(OnFloorChanged)),new
ValidateValueCallback(OnFloorValidate));

        private
static
bool OnFloorValidate(object obj)
        {
            return obj is
int && (int)obj >= 0 && (int)obj <= 3;
        }

This not only validates that the value is between 0 and 3 but also checks that the value is actually an integer. An exception is thrown if you try to set the property to an incorrect value.


 

The final class looks like:


 

    class
Lift : DependencyObject
    {
        public
static
DependencyProperty FloorProperty =
            DependencyProperty.Register(("Floor", typeof(int),typeof(Lift),new
PropertyMetadata(0,new
PropertyChangedCallback(OnFloorChanged)),new
ValidateValueCallback(OnFloorValidate));

        private
static
void OnFloorChanged(DependencyObject obj, DependencyPropertyChangedEventArgs arg)
        {
            Lift l = (Lift)obj;
            switch (l.Floor)
            {
                case 0:
                    l.Goods = "Books";
                    break;
                case 1:
                    l.Goods = "Perfumes";
                    break;
                case 2:
                    l.Goods = "Toys";
                    break;
                case 3:
                    l.Goods = "Kitchenware";
                    break;
            }
        }

        private
static
bool OnFloorValidate(object obj)
        {
            return obj is
int && (int)obj >= 0 && (int)obj <= 3;
        }

        public
int Floor
        {
            get { return (int)GetValue(FloorProperty); }
            set { SetValue(FloorProperty, value); }
        }

        public
static
DependencyProperty GoodsProperty =
        DependencyProperty.Register("Goods", typeof(string), typeof(Lift));

        public
string Goods
        {
            get { return (string)GetValue(GoodsProperty); }
            set { SetValue(GoodsProperty, value); }
        }
    }

And the XAML:


 

<Window
x:Class="TestWPF1.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:AppCode="clr-namespace:TestWPF1"
    Title="TestWPF1"
Height="300"
Width="300"
    >
    <
Window.Resources>
        <
AppCode:Lift
x:Key="Lift1"
Floor="1" />
        <
AppCode:Lift
x:Key="Lift2"
Floor="3" />
    </
Window.Resources>
    <
StackPanel
Orientation="Vertical">
        <
StackPanel
Orientation="Horizontal">
            <
TextBlock
Text="{Binding Path=Floor}" />
            <
TextBlock
Text=", " />
            <
TextBlock
Text="{Binding Path=Goods}" />
            <
StackPanel.DataContext>
                <
Binding
Source="{StaticResource Lift1}" />
            </
StackPanel.DataContext>
        </
StackPanel>
        <
StackPanel
Orientation="Horizontal">
            <
TextBlock
Text="{Binding Path=Floor}" />
            <
TextBlock
Text=", " />
            <
TextBlock
Text="{Binding Path=Goods}" />
            <
StackPanel.DataContext>
                <
Binding
Source="{StaticResource Lift2}" />
            </
StackPanel.DataContext>
        </
StackPanel>
    </
StackPanel>
</
Window>

String and Custom Aggregations using CTE and Recursive Queries

You'll all be familiar with normal aggregation.

For example I can get the number of subcategories per category in AdventureWorks:

SELECT c.Name,
COUNT(*)
as SubCategoryCount
FROM Production.ProductCategory c
JOIN Production.ProductSubCategory s
ON c.ProductCategoryID = s.ProductCategoryID
GROUP
BY c.Name

Which returns:

Name

SubCategoryCount

Accessories

12

Bikes

3

Clothing

8

Components

14


 

SQL 2005 supports 13 aggregation functions: AVG, CHECKSUM, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING, MAX, MIN, SUM, STDEV, STDEVP, VAR and VARP but nothing to aggregate strings.
You can create custom CLR aggregation functions but on some occasions, a simple SQL recursive query may be able to do what you need.

Let's have a look first at a case that doesn't require recursive queries.
Let's create a comma delimited string of all the available categories.
The data comes from the Product.ProductCategory table:

select
*
from Production.ProductCategory

And returns

ProductCategoryID

Name

rowguid

ModifiedDate

1

Bikes

CFBDA25C-DF71-47A7-B81B-64EE161AA37C

1998-06-01 00:00:00.000

2

Components

C657828D-D808-4ABA-91A3-AF2CE02300E9

1998-06-01 00:00:00.000

3

Clothing

10A7C342-CA82-48D4-8A38-46A2EB089B74

1998-06-01 00:00:00.000

4

Accessories

2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6

1998-06-01 00:00:00.000


To get the comma delimited list of Categories, we can use the COALESCE function and a string variable:

DECLARE @categories varchar(200)
SET @categories =
NULL

SELECT @categories =
COALESCE(@categories +
',','')
+
Name
FROM
Production.ProductCategory

SELECT @categories

And returns, as expected a string "Accessories,Bikes,Clothing,Components"


 

But now, how would we return the recordset of categories with a second column containing the list of subcategories.

I can get the list of subcategories for a single category but not for each category using the COALESCE method.

This is where recursive queries help. First I need to number each subcategory within the category. I can do that using RANK and ROW_NUMBER functions.


 

SELECT

    c.Name as Category,

    s.Name as SubCategory,

ROW_NUMBER()
OVER(ORDER
BY c.Name,s.Name)
+ 1
        -
RANK()
OVER(ORDER
BY c.Name)
as SubCategoryNumber
FROM Production.ProductCategory c
JOIN Production.ProductSubCategory s
ON c.ProductCategoryID = s.ProductCategoryID

And now the recursive query where each loop of the query adds to the comma delimited string with the initial value equal to the first subcategory


 

WITH


SubCategories(Category, Subcategory, SubCategoryNumber)
AS
(

    SELECT

        c.Name as Category,

        s.Name as SubCategory,
        ROW_NUMBER()
OVER(ORDER
BY c.Name,s.Name)
+ 1
            -
RANK()
OVER(ORDER
BY c.Name)
as SubCategoryNumber
    FROM Production.ProductCategory c
    JOIN Production.ProductSubCategory s
    ON c.ProductCategoryID = s.ProductCategoryID ),

TempCategories(Category, Subcategories, SubCategoryNumber)
AS
(

    SELECT Category,
CAST(Subcategory as
varchar(max)), SubCategoryNumber
    FROM SubCategories
    WHERE SubCategoryNumber = 1

    UNION ALL

    SELECT TempCategories.Category,
CAST(TempCategories.SubCategories +
','
+ SubCategories.Subcategory as
varchar(max)), TempCategories.SubCategoryNumber + 1
    FROM TempCategories
    JOIN SubCategories
    ON TempCategories.Category = SubCategories.Category and TempCategories.SubCategoryNumber + 1 = SubCategories.SubCategoryNumber ),

MaxCategories(Category, SubCategoryNumber)
AS
(

    SELECT Category,
MAX(SubCategoryNumber)
AS SubCategoryNumber
    FROM SubCategories
    GROUP
BY Category ),

Categories (Category, Subcategories)
AS
(

    SELECT TempCategories.Category, TempCategories.Subcategories
    FROM TempCategories
    JOIN MaxCategories
    ON TempCategories.Category = MaxCategories.Category AND TempCategories.SubCategoryNumber = MaxCategories.SubCategoryNumber )

SELECT
*
FROM Categories

Which returns:


 

Category

Subcategories

Components

Bottom Brackets,Brakes,Chains,Cranksets,Derailleurs,Forks,Handlebars,Headsets,Mountain Frames,Pedals,Road Frames,Saddles,Touring Frames,Wheels

Clothing

Bib-Shorts,Caps,Gloves,Jerseys,Shorts,Socks,Tights,Vests

Bikes

Mountain Bikes,Road Bikes,Touring Bikes

Accessories

Bike Racks,Bike Stands,Bottles and Cages,Cleaners,Fenders,Helmets,Hydration Packs,Lights,Locks,Panniers,Pumps,Tires and Tubes


 

If we look at each query:


 

SubCategories, we've seen already, returns the full list of categories and subcategories with a subcategory number, string at 1, for each category.

TempCategories is the recursive query and adds at each level the previous subcategory list with the current subcategory, so the first subcategory contains itself, the second catains the first and itself, the third contains the first, the second and itself and so on. We then need to only keep the last record of each category that contains every subcategory.

MaxCategories contains the highest SubcategoryNumber for each category.

Categories joins TempCategories and MaxCategory to only retain the highest subcategory number per category. The result we need.


 

The aggregation operation defined in TempCategories (here adding the string together) could be replaced with any custom aggregation. It iteratively, in a known sequence, combines the previous calculated value with the new value for that row.