.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

coalesce building pivot column headers - making them descending instead of ascending

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

Hi, I was somewhat surprised and happy to see that the t-sql statement below can be used to dynamically build my pivot column headers.   I was also surprised to see that either it is smart enought to build the headers alphabetically or I'm incredibly lucky.  Date is a char(7) column in my table with format yyyy/mm.

Anyway, it would be really cool for me if the headers were build descending instead of ascending.  I tried using a view to do this but the thing has a mind of its own.  I'll start writing a function but would like to know if the community knows of a better way. 

It generates a string that looks something like ...




@PivotColumnHeaders VARCHAR

View Complete Post

More Related Resource Links

What is the meaning of Ascending\descending sort order of a column within an index

When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

C++ Q&A: Locking Column Headers, Implementing Singleton Classes


Prevent the sizing of the column headers in an ATL composite control. Also, see how you can share a small amount of simple data among multiple processes running on the same machine without Remoting.

Paul DiLascia

MSDN Magazine June 2003

can do rowspan+columnspan in DataGrid Column Headers?

i wanna do something like this: 4 column headers: Header Font, Header Size, Cell Font, Cell Size you can see they are 2 grps, with a pair of each. now i wanna create the column headers like:     Header  |     Cell Font | Size  | Font | Size is it possible? and how to do that?

ListView fixed size column headers

Hi, I am using a ListView in its 'Detail' view. I have added several columns there and I want to restrict resizing the column width. I couldn't find a ways to do that; is there any easy way to do that? ThanksCJ

How to copy column headers along with the query result in SS00

HiIs it possible to copy the column headers along with the query result from SQL Server 2000 Query Analyser.Thanks in advance.fjz

Hiding column headers in grouped dataformwebpart

Hi, I've set up a dataformwebpart (from a SQL view) that shows a list of orders grouped by salesperson, I've succesfully created a formula that sums the total value of the orders in the group header but when I open the page the column names within the group that is collapsed are still visible. If I expand then collapse the group manually it hides the group column names as I wanted but when the page refreshes it shows the column names again! All I wanted to achieve is to have a headline figure, that is all, that is then expandable to show column names and the data, having the column names underneath the headline figure makes it look very messy.   Does anyone know why this is happening and have any suggestions? Any help greatly appreciated. Thanks

Multiple column headers in Gridview

Hi,  I've been trying for the past few hours to add mutliple headers to my grid view but keep failing to get it to show.  There's plenty of examples online that I found but can't seem to get the most simplest to work.  Below is the code I've been using with no success.  I even tried changing the "TableCell" to "TableHeaderCell" but still yields no headers as expected.  Anyone know what I could be doing wrong? protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.Header) { GridViewRow headerow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert); TableCell headercell = new TableCell(); headercell.ColumnSpan = 3; headercell.Text = "My Custom Header"; headerow.Cells.Add(headercell); GridView1.Controls[0].Controls.AddAt(0, headerow); } }

Can I customize column headers with autogeneratefields = true?


If my SQL column is UnitPrice, that's how it shows up in my templates. ok fine.

Now is there a way to customize the column header so it says "Unit Price" without changing AutoGenerateColumns to false & recreating the header & columns myself?

And if not, how can I modify the Edit.aspx fields? It was no problem with the gridview, but form view seems a little different.


Thanks for the help!!

How do I remove column headers in list view webpart?

I'd like to remove the column headers from a list view web part, as well as from a document library web part. How can I accomplish this in SharePoint 2010?

Rows to Column (PIVOT)


I have following data :

  ID  ID2  Name   Details  Value
   1   11   A      Alpha     101
   1   11   A      Beta      201
   1   11   A      Charlie   301
   1   11   A      Delta     401
   2   22   B      Alpha     102
   2   22   B      Beta      202
   2   22   B      Charlie   302
   2   22   B      Delta     402
   3   33   C      Alpha     103
   3   33   C      Beta      203
   3   33   C      Charlie   303

That needs to transform to this using TSQL:


Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. #Error when building an IIF expression for a column label




    This is a report based on two different hierarchy. PCA and CCA hierarchy.

    The @hierarchy parameter will determine which hierarchy to use and that will drive what is displayed on the row names.

    When I write the following expression, all I get is #Error.

    Parameters!hierarchy.Value = "PCA",


    If I change the expression to

    Parameters!hierarchy.Value = "PCA",

    The the PCA Hierarchy names appear but when I switch to the CCA on the @hierarchy, I get the #Error message again.


    Can any one help?




How to Add Multiple Column's Sum in Pivot Table...???


hi Fredz..


My Final Table is #Example and in that table m adding Addition of 5 Columns but its not working Properly..???


declare @columnheader varchar(max)

Select @columnheader = COALESCE(@columnheader + ',isnull([' + cast(@FieldName as varchar) + '],0)','isnull([' + cast(@FieldName as varchar)+ '],0)') 

FROM #Example



SET @query = '

SELECT * into fianltable

FROM #Example



 SUM(Col1+ Col2+ Col3Col4 + Col5)     ----In that line error will occur

----If m can change this line n taking only SUM(Col1then its Works, but i want addition of

---- FIVE columns

 FOR [MergeValue]

 IN (' + @columnheader + ')


 AS p order by ' + @columnheader + ''


Analysis Services 2008 e Excel pivot table: different filters for each column


I have to create a report in excel 2010 based on a  analysis services 2008 cube.

I created a pivot table that contains four columns containing the elements of a hierarchy. My problem is that each column should be filtered with different values of the same field.

For example: given a hierarchy that describes me a set of products, I have to filter the product for customer  1 and 3, Product B for  customer 2 and the product C for the customer 3.

I think this thing is not possible in Excel (right?)

So I decided to use a mdx script to filter each product 

I tried so (for example, product A):
scope (descendants([Products].[products].[liv0].&[A], 1, leaves));
[Measures].Quantity = sum({[Customer].[Code].&[01], [Customer].[Code].&[03]}, [Measures].quantity);
end scope;

I still get an error of infinite recursion. How can I solve my problem? Is there a better system than what I am following?

In RB 2.0 how to sort in ascending/descending order as default?

I have column say of Provider Type which I want to be ordered when report is run so that all other columns are ordered according to the order of the Provider Type values.

SSRS report export to CSV showing column headers always in English inspite of the selected locale.


We are encoutering an issue with SSRS report export to CSV showing column headers always in English, regardless of locale.


We are using SSRS to display report results in a report viewer window launched from an ASP.net page.


When the report viewer is lauched in fr-CA locale, the report viewer window content is in French. However, when the results are exported to CSV, all the column header labels inside the CSV are always in English.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend