Crosstabs in SQL

Posted on 2003-02-19
Medium Priority
Last Modified: 2012-05-04
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.
Question by:DGordo63
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 69

Expert Comment

by:Scott Pletcher
ID: 7984155
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 ' --+ ...

Expert Comment

ID: 7984982
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..
LVL 13

Expert Comment

ID: 7986465
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

Accepted Solution

__Holly__ earned 200 total points
ID: 7994803
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 blackbelt@sqlmag.com.

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'


Author Comment

ID: 7995406
I believe I got what I need so thank you for the info.


Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question