Crosstabs in SQL

Can anyone tell me how to create a crosstab report where the column headings are fluid?  Basically, I'm trying to make the values from a previous select statement and turn them into the column headings of a crosstab report.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Based on the limited info available, it looks as if only dynamic SQL could do what you want.  You can use a variable(s) to compose a SQL sting, then it.  For example:


SET @sql = 'SELECT ' + @col1 + ' AS ' + @hdg1 + ',' + @col2 + ' AS ' + @hdg2 + ',' --+ ...
+ ' FROM tableName ' --+ ...
uh subscribe to sql server magazine.

they had a good article on this just about 2 months ago.

i think that they give 3 or 4 ways to do this same thing..
A. T-SQL is not primary source for cross tables, it uses Analysis Services, but you can compute it
1. Sample
 select "Value1"=case when IdCol='Value1' then sum(MeasureCol) end
 from YourTable
 group by IdCol
2. Random number of columns can be built by dynamic query costructed by cursor
 ...cursor for select distinct IdCol from YourTable...
3. SQL limits
varchar  is limited to 8000 noninternational characters
nvarchar is limited to 4000 international characters
EXEC(...) is not limited, but you must concatenate strings directly in parameter.

B. You can crosstab grid in your application without SQL
here is the article i was talking about.

i am an ft olap developer. long term, the best way to do this is to develop an analysis services cube.

but i wouldnt plan on spending less than about a month doing that and implementnig it, and even that isnt the most flexible (until you learn MDX queries).

i would SERIOUSLY just use this code from this page:

Dynamic Crosstab Queries
Produce pivoted, denormalized output from normalized data
Editor's Note: Congratulations to Darren Brinksneader from A Technological Advantage, who submitted the T-SQL Black Belt solution that contributing editor Itzik Ben-Gan based this article on. Darren will receive $100 and a T-SQL Black Belt shirt. Send your experts-only T-SQL tips to Itzik Ben-Gan at

Crosstab queries let you store your data in a normalized manner but also let you produce pivoted, denormalized output from that data. In other words, crosstab queries let you rotate rows to columns to see different summaries of the source data. Suppose you occasionally want to get a pivoted view of your orders in which each row represents a different month, each column represents a different year, and each cell holds the number of orders for the appropriate month and year intersection. Storing your data in such a way in a table would break the first normal form, which disallows repeating groups. Crosstab queries provide a solution to that problem, but they are limited in that you usually need to know the number of destination columns (in this example, the number of order years) to write your query. But if the number of destination columns is unknown, you can use the crosstab query solution that reader Darren Brinksneader submitted and that I expand on in this article. Darren's solution uses dynamic execution to work around the crosstab query's limitation.

Static Crosstabs
Suppose you want to provide a pivoted view of the number of orders by month and year based on the Orders table in the Northwind database. If you know beforehand the possible year values, you can issue the query that Listing 1 shows.

The CASE expression, wrapped with the SUM aggregate function, adds 1 to the appropriate column only for rows that fall in the year that the column represents. The rows are grouped by month, so each row in the result will hold a different month, and each column will store the count of orders for the year that the column represents. This method is a bit tricky because the SUM function's purpose here is to perform a count, but keep in mind that you add 1s and not an actual value, which is stored in the table. You can use crosstab queries that perform other aggregate computations, such as summing values, instead of counting the number of qualifying occurrences. For example, the query in Listing 2 calculates the total quantity pivoted by store ID and order year for the Sales table in the Pubs database. Note that you sum the qualifying quantity values here instead of summing 1s. This method gives you the total quantity for each combination of store ID and order year instead of the number of qualifying rows.

Dynamic Crosstabs
Both of the previous examples rely on your knowing beforehand the values for the destination columns. But what if you don't know those values? Furthermore, as your data changes, you might need to add new columns to the result. So, you'll constantly need to be aware of the table's content and adjust your queries accordinglyunless you use dynamic execution.

Darren Brinksneader from A Technological Advantage submitted a solution that generates a crosstab view of the data in the MagicSquares table, which I presented in "Using T-SQL with Magic Squares," August 2000. The cool thing about Darren's solution is that it's dynamicin other words, it works for any number of destination columns without requiring prior knowledge of the table's data. I have extended Darren's solution and provided a stored procedure that generates a crosstab view for any table.

Let's walk through the code for the sp_CrossTab stored procedure step by step. First, take a look at the stored procedure's header, which Listing 3 shows. Note that all the elements involved are dynamic, including the table name (@table), the column that holds the values to group by (@onrows), and the column that stores the values that will appear as the destination columns (@oncols). The @onrows and @oncols parameters can accept either a column name or an expressionfor example, CustomerID or MONTH (OrderDate), respectively. If @onrows is an expression, you can supply an alias for the destination column in the @onrowsalias parameter so that the grouping column won't appear with no name in the result. The @sumcol parameter lets you specify a name for the column whose values will be summed for each intersection of @onrows and @oncols (which is how the query in Listing 2 calculated the qty column). If you don't supply a value for @sumcol, the stored procedure will perform a count of qualifying rows.

Let's proceed to the stored procedure's first stepgenerating the beginning of the dynamic SQL string, as Listing 4 shows. Step 1 starts the SQL string that will eventually produce the crosstab output. Beginning the string is a straightforward process: You store the SELECT clause, followed by the grouping column and its alias, if one was supplied.

Step 2, which Listing 5 shows, stores all the distinct key values that the stored procedure will use as the destination columns in a temporary table. Note that in this case, you don't know the data type of the grouping column beforehand, but you need to provide a data type for the column in the temporary table. I chose the nvarchar data type because I can safely assume that the stored procedure will use a numeric or character-based grouping column. You can convert both numeric and character-based values to the nvarchar data type.

Step 3 contains the stored procedure's core logic. The code in Step 3, which Listing 6 shows, iterates through all the key values in the temporary table in a loop and uses those values to generate the destination columns. The code appends those columns to the SQL string that the stored procedure gradually builds. The values that the CASE expression evaluates are also converted to the nvarchar data type to compare them with the key values from the temporary table, which are nvarchar data type values. You could skip casting these values to nvarchar because SQL Server performs an implicit casting from both numerical and non-Unicode values to Unicode values. But I don't think anyone should rely on implicit casting, so I perform an explicit casting to be safe.

Step 4, which Listing 7 shows, simply wraps up the dynamically built SQL string by adding to it the FROM, GROUP BY, and ORDER BY clauses and executing it by using the EXEC command. Now you can use the stored procedure. Listing 8 shows a few sample invocations.

Good News, Bad News
Darren's solution, which I've expanded on in this article, brings good news and bad news. The bad news is that dynamic execution is far from elegant and isn't very readable. The good news is that the stored procedure encapsulates the SQL string, so after you write the string, the string isn't visible. Also, when you create the sp_CrossTab stored procedure in the Master database and prefix the procedure with sp_, you can invoke the procedure from any database. Listing 8 shows only a few examples of how to invoke this stored procedure, but you can use the procedure for any combination of table, rows, and columns that you want.

LISTING 1: Order Count by Month and Year

USE Northwind

  MONTH(OrderDate) AS OrderMonth,
  SUM(CASE YEAR(OrderDate)
        WHEN 1996 THEN 1
        ELSE 0
      END) AS c1996,
  SUM(CASE YEAR(OrderDate)
        WHEN 1997 THEN 1
        ELSE 0
      END) AS c1997,
  SUM(CASE YEAR(OrderDate)
        WHEN 1998 THEN 1
        ELSE 0
      END) AS c1998
FROM Orders

LISTING 2: Total Quantity by Store ID and Order Year

USE Pubs

  SUM(CASE YEAR(ord_date)
        WHEN 1992 THEN qty
        ELSE 0
      END) AS c1992,
  SUM(CASE YEAR(ord_date)
        WHEN 1993 THEN qty
        ELSE 0
      END) AS c1993,
  SUM(CASE YEAR(ord_date)
        WHEN 1994 THEN qty
        ELSE 0
      END) AS c1994
FROM Sales
GROUP BY stor_id
ORDER BY stor_id

LISTING 3: Header of the sp_CrossTab Stored Procedure

USE master

  @table       AS sysname,        -- Table to crosstab
  @onrows      AS nvarchar(128),  -- Grouping key values (on rows)
  @onrowsalias AS sysname = NULL, -- Alias for grouping column
  @oncols      AS nvarchar(128),  -- Destination columns (on columns)
  @sumcol      AS sysname = NULL  -- Data cells

LISTING 4: Step 1 of the sp_CrossTab Stored Procedure: Beginning of the SQL String

  @sql AS varchar(8000),
  @NEWLINE AS char(1)


-- step 1: beginning of SQL string
SET @sql =
  '  ' + @onrows +
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''

LISTING 5: Step 2 of the sp_CrossTab Stored Procedure: Storing Keys in a Temp Table

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table

EXEC (@keyssql)

LISTING 6: Step 3 of the sp_CrossTab Stored procedure: Middle Part of SQL String

DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS c' + @key
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key

LISTING 7: Step 4 of the sp_CrossTab Stored Procedure: End of SQL String

SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

-- PRINT @sql  + @NEWLINE -- For debug
EXEC (@sql)

LISTING 8: Using the sp_CrossTab Stored Procedure

USE Northwind

EXEC sp_CrossTab
  @table       = 'Orders',
  @onrows      = 'MONTH(OrderDate)',
  @onrowsalias = 'OrderMonth',
  @oncols      = 'YEAR(OrderDate)'

USE Pubs

EXEC sp_CrossTab
  @table  = 'Sales',
  @onrows = 'stor_id',
  @oncols = 'YEAR(ord_date)',
  @sumcol = 'qty'


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DGordo63Author Commented:
I believe I got what I need so thank you for the info.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.