WPF pivoted table (vertical columns)

Source code: download

I struggled some time to find a way to create a pivoted table where the columns are shown vertical instead of horizontal.
This is a dynamic table where databinding is used to retrieve the data and by clicking the columns they also needed to be sorted.
Sorting is not included in this sample, but this is easily accomplished by adding a command to the columns (buttons).
With some googling I came accross the dependency property named "SharedSizeGroup" and that did the trick.

image 

First you create your DataTemplate with the rowdefinitions where every row has its own SharedSizeGroup.
Then you define the controltemplate for the listbox that is databound to the data.
This template also contains the same amount of rows and all rows that need to line up share the same SharedSizeGroup as defined in the DataTemplate.
If someone has a better solution, I am eager to learn!! Drop me a line.

<Window x:Class="VerticalGridColumns.Window1" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:data="clr-namespace:VerticalGridColumns" Title="Pivoted Grid" Height="300" Width="300"> <Window.Resources> <ObjectDataProvider x:Key="PersonsProvider" ObjectType="{x:Type data:Persons}" /> <ControlTemplate x:Key="HeaderButtonTemplate" TargetType="{x:Type Button}"> <Border Width="auto" Height="auto" BorderThickness="1" BorderBrush="Gray" Padding="10,3,10,3" Background="Gainsboro"> <ContentPresenter HorizontalAlignment="Center" VerticalAlignment="Center" RecognizesAccessKey="True"/> </Border> </ControlTemplate> <DataTemplate x:Key="MyDataTemplate"> <Border BorderThickness="1" BorderBrush="Gray"> <Grid ShowGridLines="True"> <Grid.RowDefinitions> <RowDefinition Height="auto" SharedSizeGroup="NameRow" /> <RowDefinition Height="auto" SharedSizeGroup="AgeRow" /> <RowDefinition Height="auto" SharedSizeGroup="GenderRow" /> </Grid.RowDefinitions> <TextBlock Grid.Row="0" VerticalAlignment="Center" Padding="3,0,3,0" Text="{Binding Path=Name}" /> <TextBlock Grid.Row="1" VerticalAlignment="Center" Padding="3,0,3,0" Text="{Binding Path=Age}" /> <TextBlock Grid.Row="2" VerticalAlignment="Center" Padding="3,0,3,0" Text="{Binding Path=Gender}" /> </Grid> </Border> </DataTemplate> <ControlTemplate x:Key="MyListBoxTemplate"> <Grid Grid.IsSharedSizeScope="True"> <DockPanel> <Border BorderThickness="1"> <Grid DockPanel.Dock="Left"> <Grid.RowDefinitions> <RowDefinition Height="auto" SharedSizeGroup="NameRow" /> <RowDefinition Height="auto" SharedSizeGroup="AgeRow" /> <RowDefinition Height="auto" SharedSizeGroup="GenderRow" /> </Grid.RowDefinitions> <Button Template="{StaticResource HeaderButtonTemplate}" Grid.Row="0">Name</Button> <Button Template="{StaticResource HeaderButtonTemplate}" Grid.Row="1" Content="Age" /> <Button Template="{StaticResource HeaderButtonTemplate}" Grid.Row="2" Content="Gender"/> </Grid> </Border> <VirtualizingStackPanel IsItemsHost="True" Orientation="Horizontal" /> </DockPanel> </Grid> </ControlTemplate> </Window.Resources> <Grid> <StackPanel Margin="10,10,10,10"> <TextBlock Text="This is an example of a pivoted grid" Margin="0,10, 0, 10" /> <ListBox x:Name="MyListBox" Height="auto" ItemTemplate="{StaticResource MyDataTemplate}" Template="{StaticResource MyListBoxTemplate}" ItemsSource="{Binding Source={StaticResource PersonsProvider}}" VerticalAlignment="Top" /> </StackPanel> </Grid> </Window>

Concatenate Column results into a single string in SQL Server 2005

I wanted to post some samples how you can accomplish column result concatenation into one string with sql server 2005.
If you know another way, let me know.

The following results are from the Categories table of the Northwind database.

image

image


Sample 1:

DECLARE @categories AS VARCHAR(1024) SELECT @categories = CASE IsNull(@categories,'') WHEN '' THEN CategoryName ELSE @categories + '; ' + CategoryName END FROM Categories ORDER BY CategoryName SELECT @categories


Sample 2:
DECLARE @categories AS VARCHAR(1024) SELECT @categories = COALESCE(@categories + '; ', '') + CategoryName FROM Categories ORDER BY CategoryName SELECT @categories AS CategoryNames


Sample 3:
DECLARE @categories AS VARCHAR(1024) SELECT @categories = CAST ( (SELECT CategoryName + '; ' AS [text()] FROM Categories FOR XML PATH('')) AS VARCHAR(1024)) SELECT @categories


Sample 4:
DECLARE @categories AS VARCHAR(1024) SELECT @categories = CAST ( (SELECT CategoryName AS CAT FROM Categories FOR XML RAW, ELEMENTS, TYPE).query ( 'for $CAT in (row/CAT) order by $CAT ascending return concat($CAT, "; ")' )AS VARCHAR(1024) ) SELECT @categories