Click here to monitor SSC
  • Av rating:
  • Total votes: 162
  • Total comments: 80
Keith Fletcher

Creating cross tab queries and pivot tables in SQL

27 March 2007

Cross tab queries in SQL Server are something that just have to be done. Keith Fletcher's T-SQL stored procedure will allow you to do it "on the fly". You can add it to your database and start cross tabbing immediately, without any further setup or changes to your SQL code.

Sometimes, you just absolutely have to generate a cross tab in SQL. It won't do to have the reporting system do it, nor is it feasible to build that functionality into the application. For example:

  • You may be using a reporting solution that doesn't provide this functionality.
  • You are using a legacy application that you'd rather not fiddle with.
  • You'd like to export some data, already set out in the required format, to a text file.

It is for these exceptional cases that I decided to write a dynamic cross tab stored procedure.

The exception rather than the rule

There is a general rule which states that data manipulation of this sort is best left to the application or reporting levels of the system, and for good reason. The SQL database engine's primary role is the storage and retrieval of information, not the complex processing of it. Anyone who has tried to pound data in SQL into a meaningful set of information, using a complicated set of business rules, will probably agree that SQL tends to discourage you from doing so, and the more fancy and creative you try to make your solution, the stronger that discouragement becomes.

It has also been said that just because you can do something, it doesn't mean you should. True, but I for one think that the opposite is also applicable. Just because it seems that you can't do something, it doesn't mean you shouldn't. It's a balancing act that demands careful consideration. I have found some applications for which this stored procedure was the ideal solution – I hinted at these in the first paragraph. However, there are just as many, if not more, where it shouldn't be used. The stored procedure can have an adverse affect on performance if not used correctly, or used on an expensive or large data source. I leave you with the advice that the script described here should be used carefully and sparingly, and not sprinkled willy-nilly about your databases.

Requirements

All of my demonstration code will use the trusty Northwind sample database. It comes with SQL Server 2000 by default, but if you've gotten rid of it, or if you're running Server 2005, you can download it from the Microsoft website.

Once Northwind has been downloaded and attached, create the sys_CrossTab stored procedure in the database and you're on your way.

A simple cross tab query

The Northwind database has a table called Categories, which is used to partition the full compliment of products into eight distinct groups, namely Beverages, Condiments, Confections, Dairy Products, Grains/Cereals, Meat/Poultry, Produce and Seafood. If the North Wind Trading Company were a real entity, it would not be inconceivable for one of the bean counters to request a report listing the total value of orders placed, by year, by category. This would be the perfect opportunity to try out a cross tab query. The simplest way to do this is to use the CASE function.

SELECT      YEAR(ord.OrderDate) YEAR, 
            SUM(CASE prod.CategoryID WHEN 1 THEN
                    det.UnitPrice * det.Quantity ELSE 0 END) Beverages,
            SUM(CASE prod.CategoryID WHEN 2 THEN
                    det.UnitPrice * det.Quantity ELSE 0 END) Condiments,
            SUM(CASE prod.CategoryID WHEN 3 THEN
                    det.UnitPrice * det.Quantity ELSE 0 END) Confections,
            SUM(CASE prod.CategoryID WHEN 4 THEN
                    det.UnitPrice * det.Quantity ELSE 0 END) [Dairy Products],
            SUM(CASE prod.CategoryID WHEN 5 THEN
                    det.UnitPrice * det.Quantity ELSE 0 END) [Grains/Cereals],
            SUM(CASE prod.CategoryID WHEN 6 THEN   
                    det.UnitPrice * det.Quantity ELSE 0 END) [Meat/Poultry],
            SUM(CASE prod.CategoryID WHEN 7 THEN
                    det.UnitPrice * det.Quantity ELSE 0 END) Produce,
            SUM(CASE prod.CategoryID WHEN 8 THEN
                    det.UnitPrice * det.Quantity ELSE 0 END) Seafood

FROM        Orders ord

INNER JOIN  [Order Details] det
ON          det.OrderID = ord.OrderID

INNER JOIN  Products prod
ON          prod.ProductID = det.ProductID

GROUP BY    YEAR(ord.OrderDate)

ORDER BY    YEAR(ord.OrderDate)

This will return

So, you quickly type up the query, you show the accountant how to import the data into an Excel spreadsheet, and you're off for a pint to celebrate your ingenuity.

Shortly thereafter, the chap decides that the data report is not quite granular enough, and would like a similar report split by product name rather than category. There are 77 products, so it involves a few more CASE statements. You grumble to yourself quietly while demonstrating your cut-and-paste proficiency and generate the new report, showing the breakdown by product.

Thanks to your report, the company decides that a few of the product lines are not generating the revenue that they should, so they drop those products and add a few new ones. The accountant is dismayed to discover that the report you wrote for him still shows the old products, and has not included the new products into the report. This is where your quick solution starts to go south.

Enter the dynamic cross tab

There comes a point when maintaining all of these 'hard-coded' cross tabs is more effort than spending some time developing a more generic, permanent solution. The solution I arrived at still essentially uses the CASE function to cross tab the data. The only real difference is that the list of CASE statements is built up dynamically, based on the data that you wish to use to describe the columns.

The stored procedure I created started as a simple dynamic CASE statement builder, using sp_executesql. It immediately became useful and soon people were asking, "How do I get it to do..." questions. Bit by bit, it evolved to the monster it is today. The intention has always been to have a procedure that was so generic and portable, that it could be added to anyone's database and cross tabs could be created immediately without any further setup or change in SQL code. Although simplicity of use may have suffered a little, I feel that the primary objective has been achieved.

Using the stored procedure

For starters, let's generate a cross tab result set giving a list of companies in the first column, the name of the contact at the company in the second column and a list of the stocked products from column three onwards. Inside the grid, we'll give the total value of the orders placed by that company, for that product. It must be sorted by company name.

The SQL query that returns the source data that we require is

SELECT          cus.CompanyName, cus.ContactName, prod.ProductID, 
                prod.ProductName, det.UnitPrice, det.Quantity
      
FROM            Orders ord

INNER JOIN      [Order Details] det
ON              det.OrderID         = ord.OrderID

INNER JOIN      Products prod
ON              prod.ProductID      = det.ProductID

INNER JOIN      Customers cus
ON              cus.CustomerID      = ord.CustomerID
 And here's how we'll do it: EXEC sys_CrossTab
    'Orders ord
    inner join      [Order Details] det
    on              det.OrderID         = ord.OrderID
    inner join      Products prod
    on              prod.ProductID      = det.ProductID
    inner join      Customers cus
    on              cus.CustomerID      = ord.CustomerID'
, --  @SQLSource    
    'prod.ProductID',                                      --  @ColFieldID   
    'prod.ProductName',                                    --  @ColFieldName 
    'prod.ProductName',                                    --  @ColFieldOrder
    'det.UnitPrice * det.Quantity',                        --  @CalcFieldName
    'cus.CompanyName, cus.ContactName',                    --  @RowFieldNames
    NULL,                                                  --  @TempTableName
    'sum',                                                 --  @CalcOperation
    0,                                                     --  @Debug        
    NULL,                                                  --  @SourceFilter 
    0,                                                     --  @NumColOrdering
    'Total',                                               --  @RowTotals   
    NULL,                                                  --  @ColTotals   
    'CompanyName',                                         --  @OrderBy     
    'int'                                                  --  @CalcFieldType

The first few rows and columns returned will be

Structure of the stored procedure

If you wish to fine-tune the procedure, make it more efficient, maybe adapt it to your individual needs and cut out some of the functionality you'll never use, you may be interested in how it was put together. If you've ideas of a better way of doing things, then please do share it with all of us. The stored procedure is fairly well documented and you should be able to find your way around the code.

You'll notice that there are a good few varchar(8000) variable declarations right up front. Very early into the project, I found that varchar(8000) just wasn't large enough for anything beyond the most trivial query. The only way around this storage problem was to create a range of these variables, and as the first one filled up, I'd start adding information into the next. A range of variables have been declared for each portion of the final query that we are building, namely the CASE statements, the select field list, the totals and so on.

The first order of business is to determine the names of the columns of the cross tab. This will be the first of two queries on your source data. We insert all distinct column names into a memory table (#Columns), in the order that they should appear in the cross tab. If you've chosen to show column totals, these will be calculated and stored at this point.

Next, any prefixes from the row fields are stripped out. This is important, as we'll be grouping by these fields and the aliases, or table references, can complicate the generated query.

I then define a cursor that runs over the items that were inserted into the #Columns memory table. This generates the CASE statements that are used to perform the aggregate functions on the source data. Some work is also done on the generation of the SQL statement portions for row and column totals, as well as the insert statement into the target temporary table, if these options were selected.

Once we've built up the bits and pieces, we string them together and run the query. If you look into the stored procedure code, you'll see that I've identified eight different scenarios, based on whether or not we've elected to save to a temp table or use row and column totals. The applicable scenario is determined and the final SQL statement is then pieced together appropriately, along with the debug version if debugging was enabled. This will be the second query on your data source.

It would be difficult to describe the stored procedure in more detail than this, without getting terribly long winded about it. However, I do feel that the code is adequately commented and you shouldn't have too much hassle making modifications should you choose to do so. The best advice I have to offer is to make use of the debugging facility, as you'll immediately see the effect of your change on the generated SQL code.

The stored procedure parameters, explained

The prototype of the stored procedure is as follows:

CREATE PROC [dbo].[sys_CrossTab]
   
@SQLSource        varchar(8000),
    @ColFieldID       varchar(8000),
    @ColFieldName     varchar(8000),
    @ColFieldOrder    varchar(8000),
    @CalcFieldName    varchar(8000),
    @RowFieldNames    varchar(8000),
    @TempTableName    varchar(200) = null,
    @CalcOperation    varchar(50) = 'sum',   
    @Debug            bit = 0,
    @SourceFilter     varchar(8000) = null,
    @NumColOrdering   bit = 0,
    @RowTotals        varchar(100) = null,
    @ColTotals        varchar(100) = null,
    @OrderBy          varchar(8000) = null,
   
@CalcFieldType    varchar(100) = 'int'

My original application didn't have need of nvarchars, and I really needed the extra storage space, so I decided to use the varchar data type. I would recommend that you alter these to nvarchars if you want code that is culture-safe.

Some detail of the purpose and usage of each parameter is given. If my description is a little too vague for you, have a look at the example script above and the output it generated, or even better, run the script for yourself and experiment with it.

@SQLSource

The first parameter, @SQLSource, is just that; the source of the data you wish to generate the cross tab from. This can be a table name, view name, function name or even the FROM clause of a SELECT statement, as we've used in the example. Have another look at the SQL statement I presented, and compare it to the text used for the @SQLSource parameter. It's basically the portion of the SQL statement from after the FROM keyword, up to but not including the WHERE clause, if one exists. If you wish to use a table, view or function, use just the name and possibly its alias - leave out the SELECT keyword.

@ColFieldID

We need to decide, for each row in the source data, which column to assign the values to. The @ColFieldID parameter is used to select the column to be used for this function. The ProductID field is used in our example. The number of distinct values that this column has in the source data will tell you how many columns will be used in the cross tab. This is an important consideration, especially if you wish to use the results of the cross tab in an Excel spreadsheet, as Excel puts an upper limit on the number of columns that it can handle.

@ColFieldName

Use @ColFieldName to provide the name of the field that will contain the captions for each column of the cross tab. It can be the same field as used for @ColFieldID.

@ColFieldOrder

If you require the columns to be sorted, you can specify a field by which the ordering should occur. The @ColFieldOrder parameter should hold the name of this ordering field. This too can be the same field as @ColFieldID. You might also want to set the @NumColOrdering parameter if the ordering is important. By default, the columns will be sorted alphanumerically. If you require then to be sorted numerically, set @NumColOrdering to 1. The description of that parameter will give a little more detail.

@CalcFieldName

@CalcFieldName should contain the name of the field that will be used to create the data within the cross tab grid. This will be the base data of the count, sum, average or whichever aggregate function you choose. Naturally, you should ensure that the data type of this field matches the operation you wish to perform. You cannot perform a SUM operation on varchar field, although a COUNT operation is perfectly acceptable.

@RowFieldNames

Here you will provide a comma-separated list, consisting of one or more field names, to be used as the first few columns of the grid. The aggregate function that you intend to perform will be carried out as a function of the grouping of the fields you specify here, so choose them wisely.

@TempTableName

Occasionally, the cross tab is not the final result, but a means to an end. Maybe you'd like to perform further queries on the cross tab data generated, or you'd like to join it to other tables. The @TempTableName parameter was added for this reason. It provides a way for the cross tab data to be inserted into a temporary table that you can then use for further processing.

There are a number of caveats here though. Firstly, you'll need to create the temp table before you call the cross tab stored procedure (because of SQL's scoping rules). When creating a table, you'll need to provide at least one column though. The simplest is to do something like

CREATE TABLE #CrossTab (Dummy TINYINT NULL)

You will then pass in the name of the temp table (#CrossTab in this case) to the stored procedure. Once the cross tab generation has completed, your temp table will contain the cross tab information in addition to your Dummy field. If, like me, you feel that the dummy field is 'wasted', you can declare it as an identity field, thereby adding a sequence number to your table.

CREATE TABLE #CrossTab (Sequence INT IDENTITY(1,1))

The users of your query are a lot less likely to be perturbed by a sequence number than an empty, useless column at the front of the result set.

@CalcOperation

Here we tell the stored procedure what to do with the source data we're providing. Acceptable values for this parameter are any of SQL's aggregate functions, namely AVG, SUM, COUNT, MIN, MAX and their ilk. Make sure that you match the operation to the data type, i.e. no SUMming of varchar data.

@Debug

The @Debug parameter, switched off by default, can be quite handy. When enabled (set to 1), it will print out the SQL code used to generate the cross tab. If you're not expecting the columns of your cross tab to alter, you can run the SQL printed out by the debugging code instead of using the stored procedure, which will be considerably more efficient. In this way, you can use the stored procedure as a SQL generation tool.

Take note that the row totals will not be calculated by the debug SQL. The stored procedure will 'hardcode' the totals that it calculated at the time that it was run.

@SourceFilter

@SourceFilter lets you input some SQL code to filter the source data prior to it been cross tabbed. This would be the code of the WHERE clause to match that of the SELECT clause as given to the @SQLSource parameter. There is no reason why you can't include a WHERE clause as part of the data given to @SQLSource, although I find it easier and more maintainable to specify it separately.

@NumColOrdering

If you intend for your columns to be arranged in a particular order, you'll give the field name to order them by to the @ColFieldOrder parameter, and you'll use the @NumColOrdering field to specify how the ordering is to take place. A value of 0 (the default) will cause the data to be sorted alphanumerically, and a value of 1 will sort in numerically.

If you're not sure about the difference between the two, consider the following list: 2, 1, 10, 11, 20, 100. When this is sorted numerically, it will be 1, 2, 10, 11, 20, 100. However, sorting it alphanumerically will result in 1, 10, 100, 11, 2, 20. Naturally, alphanumeric sorting will also handle A's, B's and C's, whereas numeric sorting will cause a type mismatch error to be raised.

@RowTotals

If this parameter is set to something other than NULL, an additional column will be added as the final column of the result set (the column name being the value given here), and will contain the sum of the cross tab values for each row.

@ColTotals

If set to something other than NULL, an additional row will be added as the final row of the result set, and will contain the sum of the cross tab values for the each column. There are a number of things to look out for with this one though. Firstly, you'll need to pass the field names already wrapped in quotes into the parameter. For example, if you wish the line to be marked as Total, you'll need to set @ColTotals to '''Total'''. Secondly, you'll need to provide as many values as fields that you've specified in the @RowFieldNames parameter. In our example, we've used two fields, so we need to provide two values to @ColTotals. Lastly, this total row may not necessarily appear at the bottom of the result set, depending on whether you've given an @OrderBy parameter value. The totals are added prior to the cross tab being sorted.

If you've enabled the debug printing option, the SQL code given to you will also not calculate the column totals dynamically. The totals will have been determined during the initial execution of the stored procedure, and these fixed values are then joined onto the rest of the result set.

@OrderBy

The @OrderBy parameter allows you to provide an ORDER BY clause. If used, this must be one or more of the fields used in the @RowFieldnames parameter. If you're using @ColTotals, keep in mind that the column totals row will be considered part of the cross tab data, and will be ordered along with the other rows.

@CalcFieldType

The data type of the calculated fields in the cross tab grid can be specified by the @CalcFieldType parameter. This will be INT types by default. Set the type to one that is appropriate for the type of operation being performed, and the type of data you expect to see in the cross tab.

The challenge!

If you're going to try the stored procedure out, you may as well get something for your effort. The Simple-Talk editor, Tony Davis, has kindly offered to sponsor a prize for the first three correct responses to the challenge. It is also based on the Northwind database, and you'll need to do the following:

Compile a cross tab report that displays the order value by customer, by quarter. You should also group the clients by the country in which they are based. Sort the list by country, and then by company name. Show both row and column totals, to appear at the right and bottom of the report respectively. I've included a screen shot so that you can see what the report should look like.

Post the source code for your solution in the comments to this article, (or send it to Tony at editor@simple-talk.com).

In conclusion

You'll find that once you've done one cross tab, you've pretty much done them all. The greatest difficulty is in actually deciding what you want displayed, and then collecting the source data for the stored procedure. The actual generation of the cross tab is then simply a matter of matching the field names to the input parameters.

I hope that you'll find this stored procedure as helpful as I have - it's one of the more valuable items in my toolbox. If you discover some novel use for it, or a new idea on how to improve it a little, please share it with us. I for one would be interested to hear about it.

Keith Fletcher

Author profile:

Keith is currently employed as a software developer, and has specialised in SQL Server over the last 8 years. Based in Johannesburg, South Africa, he has dabbled in project management, network support, electrical engineering and a few other bits and bobs that people were willing to pay him for. There have been rumours that he’s a bit of a techno-geek.

Search for other articles by Keith Fletcher

Rate this article:   Avg rating: from a total of 162 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: @ColTotals
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 2:01 AM
Message: Can you give an example of the syntax for this one? Don't really understand what you mean by passing in more than one "value"

Subject: @ColTotals
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 2:04 AM
Message: Can you give an example of the syntax for this one? Don't really understand what you mean by passing in more than one "value"

Subject: @ColTotals
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 2:34 AM
Message: Sure. Take the sample script that I used in the article and replace the @ColTotals parameter value (current null) with 'null, ''Grand Total'''. If you now run the script, you'll see the effect that it has.

Subject: @ColTotals
Posted by: KeithFletcher (view profile)
Posted on: Thursday, March 29, 2007 at 2:37 AM
Message: <pre>
EXEC sys_CrossTab
'Orders ord
inner join [Order Details] det
on det.OrderID = ord.OrderID
inner join Products prod
on prod.ProductID = det.ProductID
inner join Customers cus
on cus.CustomerID = ord.CustomerID', -- @SQLSource
'prod.ProductID', -- @ColFieldID
'prod.ProductName', -- @ColFieldName
'prod.ProductName', -- @ColFieldOrder
'det.UnitPrice * det.Quantity', -- @CalcFieldName
'cus.CompanyName, cus.ContactName', -- @RowFieldNames
NULL, -- @TempTableName
'sum', -- @CalcOperation
0, -- @Debug
NULL, -- @SourceFilter
0, -- @NumColOrdering
'Total', -- @RowTotals
'null, ''Grand Total''', -- @ColTotals
'CompanyName', -- @OrderBy
'int' -- @CalcFieldType
</pre>

Subject: Almost cracked it but..
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 2:39 AM
Message: Own up, who's cheating!? Got it the same as the screenshots but grand totals is being set to Sequence 1 (using temp table) when I assign first col to NULL the same as is shown. I'm assuming we're not supposed to change to cross tab query to fix this?

Subject: Typo
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 2:46 AM
Message: I meant to say, I assume we're not supposed to change the code of the cross tab query? :)

Subject: RE: Typo
Posted by: KeithFletcher (view profile)
Posted on: Thursday, March 29, 2007 at 2:54 AM
Message: Nothing's stopping you from manipulating the data AFTER you've cross tabbed it...

Subject: Yes but..
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 3:08 AM
Message: In the example shown there is a sequence column. I'd assume that's because a temp table has been passed in. The sequence in the example is being returned differently than what I'm getting.

Manipulating data after the cross tab I'd considered, but you can't amend identity columns so I wondered how in the screenshot, grand total had been set to Sequence 90 - or rather if the cross tab had somehow returned it like that.

There's a couple of ways I could get around it but I'd class them both as "cheating" so thought I might be missing something obvious, or some mystic DB settings are causing my NULL values to be ordered differently.

Subject: RE: Yes but..
Posted by: KeithFletcher (view profile)
Posted on: Thursday, March 29, 2007 at 3:19 AM
Message: I guess then that I "cheated" when I coded the SQL to generate the screen shot. Work around it in anyway you feel appropriate.

Subject: Code
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 3:28 AM
Message: Very nifty sp by the way, we've had a couple of occasions this would have been of use!

Alright then. I guess in a report you'd hide the sequence columns anyway so could do it like this. I have to admit I'm guilty in not knowing how to amend an identity column value (I searched around MSDN) so I got around it a bit differently..

-- We don't hold ther quarter literal anywhere so create a table holding it with each associated order
SELECT OrderID,
CAST(DATEPART(yyyy,OrderDate) as char(4)) + '-Q' + CAST(DATEPART(QQ,OrderDate) as char(1)) as 'Quarter'

INTO dbo.OrderQuarters
FROM dbo.Orders


-- Use Cross-tabbing to report with quarters as col headers
DROP TABLE #CrossTab

CREATE TABLE #CrossTab (Sequence INT IDENTITY(1,1))

EXEC sys_CrossTab

'dbo.Orders ORD

INNER JOIN dbo.[Order Details] DET
ON DET.OrderID = ORD.OrderID

INNER JOIN dbo.OrderQuarters OQ
ON OQ.OrderID = ORD.OrderID

INNER JOIN dbo.Customers CUS
ON CUS.CustomerID = ORD.CustomerID',
'OQ.Quarter',
'OQ.Quarter',
'OQ.Quarter',
'det.UnitPrice * det.Quantity',
'cus.Country, cus.CompanyName',
'#CrossTab',
'SUM',
0,
NULL,
0,
'Total',
'NULL,''Grand Total''',
'Country, CompanyName',
'Money'


-- This isnt' right, Grand total at top
SELECT * FROM #CrossTab
ORDER BY Sequence

-- Sort it out
DECLARE @MaxSeq Int
SELECT @MaxSeq = MAX(Sequence) FROM #CrossTab

SELECT CASE WHEN CompanyName = 'Grand Total' THEN @MaxSeq ELSE Sequence - 1 END as NewSeq,*
FROM #CrossTab
ORDER BY
NewSeq




Subject: Oops
Posted by: JRMorris (view profile)
Posted on: Thursday, March 29, 2007 at 3:45 AM
Message: Forgot to log in. And the DROP TABLE line above should be commented out.

Thanks for the article.

Subject: Alternative...
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 04, 2007 at 2:59 AM
Message: Hi,

Ever looked at this one (it looks simpler !) :

http://www.sqlteam.com/item.asp?ItemID=2955

Kind regards,
Danny

Subject: Thats Great
Posted by: Mdafaiz (view profile)
Posted on: Wednesday, April 04, 2007 at 7:21 AM
Message: Helpfull .Could you post XP_sendmail.
Faiz Farazi.
Dhaka,Bangladesh
www.databasetimes.net

Subject: Hmmm...
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 04, 2007 at 8:32 AM
Message: How's this better than the PIVOT statement built into SQL 2005?

Seems to me to be a pretty expensive procedure to run, but might be worth the price if one doesn't have other means to turn this data around for the client.

Subject: RE: Alternative...
Posted by: KeithFletcher (view profile)
Posted on: Wednesday, April 04, 2007 at 11:49 AM
Message: Hi there.

Yes, it's considerably simpler, but it does suffer from a few problems (the author mentions them himself), all of which were show-stoppers for me. The start of my code come from a query very similar to this...

Subject: RE: Hmmm...
Posted by: KeithFletcher (view profile)
Posted on: Wednesday, April 04, 2007 at 11:54 AM
Message: It's probably not better than SQL 2005's PIVOT statement, but SQL 2000 and earlier don't have something like this, so I had to make do...

Subject: it is probably better than sql 2005's built in pivot...
Posted by: Beej (view profile)
Posted on: Wednesday, April 04, 2007 at 10:39 PM
Message: ... because the built in pivot doesn't work on a dynamic resultset. you have to "hard code" all of the output columns in your query... as soon as you want something more you're off into dyn-sql gymnastics... i could post my version of a dynamic pivot_s if anybody is interested. To me this kind of stuff is awesome on the db... and don't artificually pigeon hole the abstraction we call a relational database into the Nth tier only... i feel like having a mini-DB at the n-tier web/app server level makes wonderful sense (if you happen to like being productive with your relational syntax knowledge)... i'm even more tickled that microsoft's LINQ technology is finally going to take this perspective main stream... and we can finally stop trying to convince the non believers that syntax and programming approaches should be considered viable at all levels until legitimately proven otherwise... whew guess that pulled a rant out of me :) btw: here's my best example to date of cross-tabbing a product attribute datamodel: http://www.lesco.com/ProdCatalog/ProdCatalogNav.aspx?SKUGroupID=104 check out the "item specifications tab" in addition to the immediately visible pricing grid... all that data is stored as SKU-to-attribute assoc's and then pivoted in stored procs for better human consumption... man i love this model!!!

Subject: Amazing
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 11, 2007 at 6:05 AM
Message: Thank you for this brilliant time saver!

Subject: SQL Server 2000
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 25, 2007 at 8:25 AM
Message: Please, it is haste, help me. Im absolute beginer and I wont to run something like this code on SQL Server 2000 without errors:

SELECT id, [a], [b]
FROM (SELECT id, loc, qty FROM iloc) p
PIVOT
(
AVG(qty)
FOR loc IN ([a], [b])
) AS pvt
------------
on SQL Server 2005 it works OR most accurate:

SELECT id, *
FROM (SELECT id, loc, qty FROM iloc) p
PIVOT
(
AVG(qty)
FOR loc IN (*)
) AS pvt

* = all fields in the table

Sorry for my bad english :) Thanks A Lot.

Subject: Re: SQL Server 2000
Posted by: KeithFletcher (view profile)
Posted on: Thursday, April 26, 2007 at 2:17 AM
Message: Hi there, sorry for not responding sooner.

I'm taking a guess at what you want to do. Try this:

EXEC sys_CrossTab
'iloc', -- @SQLSource
'loc', -- @ColFieldID
'loc', -- @ColFieldName
'loc', -- @ColFieldOrder
'qty', -- @CalcFieldName
'id', -- @RowFieldNames
NULL, -- @TempTableName
'avg'

Let me know if it helps...

Subject: Re:"Re: SQL Server 2000"
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 26, 2007 at 7:48 AM
Message: THANK YOU THIS WORKS! :))
Other problem - how to insert result into a table or view.

Subject: Re: SQL Server 2000
Posted by: KeithFletcher (view profile)
Posted on: Thursday, April 26, 2007 at 8:55 AM
Message: Sure, you can insert the results into a temp table. Here's an example:

create table #CrossTab (Sequence int identity(1,1))

EXEC sys_CrossTab
'iloc', -- @SQLSource
'loc', -- @ColFieldID
'loc', -- @ColFieldName
'loc', -- @ColFieldOrder
'qty', -- @CalcFieldName
'id', -- @RowFieldNames
'#CrossTab', -- @TempTableName
'avg' -- @CalcOperation

select * from #CrossTab

drop table #CrossTab

Subject: Alias and other things..
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 02, 2007 at 8:59 AM
Message: I have this base query:

SELECT i.item, i.description, prodcode.description AS Expr1, il.loc, l.description AS Expr2, il.qty_on_hand
FROM item AS i INNER JOIN itemloc AS il ON i.item = il.item INNER JOIN location AS l ON il.loc = l.loc INNER JOIN prodcode ON i.product_code = prodcode.product_code


What is wrong. How I to make this from below to work:


CREATE TABLE #CrossTab(Sequence INT IDENTITY(1,1))

EXECUTE [dbo].[sys_CrossTab]
'item AS i INNER JOIN itemloc AS il ON i.item = il.item INNER JOIN location AS l ON il.loc = l.loc INNER JOIN prodcode ON i.product_code = prodcode.product_code', -- @SQLSource
'il.loc', -- @ColFieldID
'l.description AS Expr2', -- @ColFieldName
'il.loc', -- @ColFieldOrder
'il.qty_on_hand', -- @CalcFieldName
'i.item, i.description, prodcode.description AS Expr1', -- @RowFieldNames
'#CrossTab', -- @TempTableName
'avg', --@CalcOperation
'1', --@Debug
NULL, --@SourceFilter
'0', --@NumColOrdering
NULL, --@RowTotals
NULL, --@ColTotals
NULL, --@OrderBy
'int' --@CalcFieldType

SELECT j.job, c.*
FROM #CrossTab AS c INNER JOIN job AS j ON c.item = j.item

DROP TABLE #CrossTab


When I leave in @RowFieldNames only i.item and in @ColFieldName il.loc I see this error:

Msg 468, Level 16, State 9, Line 23
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
Msg 209, Level 16, State 1, Line 61
Ambiguous column name 'item'.

Subject: RE: Alias and other things..
Posted by: KeithFletcher (view profile)
Posted on: Wednesday, May 02, 2007 at 10:23 AM
Message: First of all, remove the aliases from the parameters, as follows:

EXECUTE [dbo].[sys_CrossTab]
'item AS i INNER JOIN itemloc AS il ON i.item = il.item INNER JOIN location AS l ON il.loc = l.loc INNER JOIN prodcode ON i.product_code = prodcode.product_code', -- @SQLSource
'il.loc', -- @ColFieldID
'l.description', -- @ColFieldName
'il.loc', -- @ColFieldOrder
'il.qty_on_hand', -- @CalcFieldName
'i.item, i.description, prodcode.description', -- @RowFieldNames
'#CrossTab', -- @TempTableName
'avg', --@CalcOperation
'1', --@Debug
NULL, --@SourceFilter
'0', --@NumColOrdering
NULL, --@RowTotals
NULL, --@ColTotals
NULL, --@OrderBy
'int' --@CalcFieldType

That will get the stored procedure running correctly.

As for the error message, you have conflicting collation settings on your server. A quick search on the Simple-Talk website revealed these links:

http://www.simple-talk.com/community/forums/thread/913.aspx#919

http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-string-manipulation-workbench/

Doing a search on Google will give you far more information than you'll ever need. You'll need to do a bit of research and fault finding to sort it out.

Unfortunately I can't assist you further with your collation conflict, as I'd need to know a lot more about your environment, and secondly, collation wasn't the topic of this article.

Hope you get sorted out...

Subject: RE: Alias and other things..
Posted by: KeithFletcher (view profile)
Posted on: Wednesday, May 02, 2007 at 10:36 AM
Message: As an after thought, try this approach:

First, put all the data you need to work with into a temp table:


SELECT i.item, i.description, prodcode.description AS Expr1, il.loc, l.description AS Expr2, il.qty_on_hand
INTO #SourceData
FROM item AS i INNER JOIN itemloc AS il ON i.item = il.item INNER JOIN location AS l ON il.loc = l.loc INNER JOIN prodcode ON i.product_code = prodcode.product_code


Create your results temp table:


create table #CrossTab (Sequence int identity(1,1))


Next, run the stored procedure:


EXECUTE [dbo].[sys_CrossTab]
'#SourceData', -- @SQLSource
'loc', -- @ColFieldID
'Expr2', -- @ColFieldName
'loc', -- @ColFieldOrder
'qty_on_hand', -- @CalcFieldName
'item, description, Expr1', -- @RowFieldNames
'#CrossTab', -- @TempTableName
'avg', --@CalcOperation
'1', --@Debug
NULL, --@SourceFilter
'0', --@NumColOrdering
NULL, --@RowTotals
NULL, --@ColTotals
NULL, --@OrderBy
'int' --@CalcFieldType


Do your join:

SELECT j.job, c.*
FROM #CrossTab AS c INNER JOIN job AS j ON c.item = j.item



Subject: What If...
Posted by: Anonymous (not signed in)
Posted on: Friday, May 04, 2007 at 7:56 AM
Message: Looking at a report I am trying to make. I have customer number, name, order_date, order type. Order type is a 3 char code. I would like to create a pivot type report with dates as columns and some extra columns for %'s. Original Row

Cust#,CustName,Date,Ordertype
001, John Doe, 070101, ABC
001, John Doe, 070102, ABC
001, John Doe, 070103, XXX
002, Jane Doe, 070101, ABC
002, Jane Doe, 070102, ABC
002, Jane Doe, 070103, ABC

Report Output

Cust # Cust Name 070101 070102 070103 %1 %2 %3
001 John Doe ABC ABC XXX 66 34 100
002 Jane Doe ABC ABC ABC 100 00 100

My dates columns should always be the last 6 order dates. Can this be modified to work since I need multiple columns based off the same column name? Also I need some average columns that would base it off char value. ABC is there 66% of the time for cust 001 for example.

Right now I have a view to create the last 6 days, using report services to create a matrix, exporting that to excel so I can get the final 3 columns. I know there has to be a better way

Subject: What If...
Posted by: Anonymous (not signed in)
Posted on: Friday, May 04, 2007 at 8:06 AM
Message: Looking at a report I am trying to make. I have customer number, name, order_date, order type. Order type is a 3 char code. I would like to create a pivot type report with dates as columns and some extra columns for %'s. Original Row

Cust#,CustName,Date,Ordertype
001, John Doe, 070101, ABC
001, John Doe, 070102, ABC
001, John Doe, 070103, XXX
002, Jane Doe, 070101, ABC
002, Jane Doe, 070102, ABC
002, Jane Doe, 070103, ABC

Report Output

Cust # Cust Name 070101 070102 070103 %1 %2 %3
001 John Doe ABC ABC XXX 66 34 100
002 Jane Doe ABC ABC ABC 100 00 100

My dates columns should always be the last 6 order dates. Can this be modified to work since I need multiple columns based off the same column name? Also I need some average columns that would base it off char value. ABC is there 66% of the time for cust 001 for example.

Right now I have a view to create the last 6 days, using report services to create a matrix, exporting that to excel so I can get the final 3 columns. I know there has to be a better way

Subject: RE: What If...
Posted by: KeithFletcher (view profile)
Posted on: Friday, May 04, 2007 at 9:06 AM
Message: This should be doable. Modify your query so that it includes the date and percentage data (probably with a union). From what I can see of your data, it should be easy enough to generate the query to do this. The data should look as follows:

Cust#,CustName,ColData,Ordertype
001, John Doe, 070101, ABC
001, John Doe, 070102, ABC
001, John Doe, 070103, XXX
002, Jane Doe, 070101, ABC
002, Jane Doe, 070102, ABC
002, Jane Doe, 070103, ABC
001, John Doe, '%1', 66
001, John Doe, '%2', 34
001, John Doe, '%3', 100
002, Jane Doe, '%1', 100
002, Jane Doe, '%2', 100
002, Jane Doe, '%3', 100


Then, run the stored proc...


EXECUTE sys_CrossTab
'<your query>', -- @SQLSource
'ColData', -- @ColFieldID
'ColData', -- @ColFieldName
'ColData', -- @ColFieldOrder
'Ordertype', -- @CalcFieldName
'Cust#,CustName', -- @RowFieldNames
null, -- @TempTableName
'max' --@CalcOperation


That should get you started...

Subject: What if update
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 09, 2007 at 2:55 PM
Message: Sorry, I wasn't clear. The last 3 columns (%'s) are a column that is outsidt the table. Those are figured out at report time (currently in excel).

Cust#,CustName,Date,Ordertype
001, John Doe, 070101, ABC
001, John Doe, 070102, ABC
001, John Doe, 070103, XXX
002, Jane Doe, 070101, ABC
002, Jane Doe, 070102, ABC
002, Jane Doe, 070103, ABC

That is the only information in this table. But your post has me thinking. Would it be better to have another table that held that info and just joined them? The main problem I have is how to figure out the "count ordertype divide by 6" within SQL. Using IF's in excel to do it now (unless I just need to use VB).

Subject: RE: What if update
Posted by: KeithFletcher (view profile)
Posted on: Thursday, May 10, 2007 at 2:01 AM
Message: Yes, calculating in SQL is by far the easiest solution. The script below will calculate your percentages (you'll need to add all the extra bits, like checking for division by zero etc.)

select O.Cust#, O.OrderType, O.OrderTypeCount * 100 / C.OrderCount
from
(select Cust#, OrderType, count(OrderType) OrderTypeCount
from <tablename>
group by Cust#, OrderType ) O
left join
(
select Cust#, count(Cust#) OrderCount
from <tablename>
group by Cust#) C
on O.Cust# = C.Cust#


Join this back onto your source data in <tablename> (as described previously), and you're away...

Subject: What if update
Posted by: Anonymous (not signed in)
Posted on: Friday, May 11, 2007 at 7:34 AM
Message: I will give this a try. Thanks!

Subject: Large questionnaire crosstab
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 22, 2007 at 4:17 AM
Message: Hi Keith,
Many thanks for you topic. It's help me a lot, but still not solve out one of my biggest trouble.
I have a very big questionnaire data, need to cross-tabbed it into column, but the trouble is there are too much column for one record.
Basically, I can manual make it 'like' cross-tabbed using CASE or LEFT OUTER JOIN, but looking for more robust and adaptively way by using dynamic crosstab StoreProc.When I run your StoreProc as :
EXEC sys_Crosstab
'questionnaireentry',
'invitationcallid',
'questionid',
NULL,
NULL,
NULL

It generate errors :
Server: Msg 50000, Level 16, State 1, Procedure sys_CrossTab, Line 529
Col level was too high (ran out of variables). 51
Server: Msg 50000, Level 16, State 1, Procedure sys_CrossTab, Line 529
Col level was too high (ran out of variables). 52
...
Server: Msg 220, Level 16, State 2, Procedure sys_CrossTab, Line 526
Arithmetic overflow error for data type tinyint, value = 256.
Server: Msg 50000, Level 16, State 1, Procedure sys_CrossTab, Line 529
Col level was too high (ran out of variables). 255
.....

FOr many crosstab scripts I've collected and modified, Still connot figure out how to solve the "too many columns" problem.
Anyone here have an idea for this trouble ?

Best regrads

Subject: Large questionnaire crosstab
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 22, 2007 at 6:24 AM
Message: Me again. Do a Googling ang found this :
http://www.geckoware.com.au/Content.aspx?Doc_id=1002

Downloaded and gave a tried. And it's worked !
Now I'm wonder why they can do that ?
(Another similar is http://www.rac4sql.net/index.asp )

Subject: Large questionnaire crosstab
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 22, 2007 at 9:01 PM
Message: Me again. Do a Googling ang found this :
http://www.geckoware.com.au/Content.aspx?Doc_id=1002

Downloaded and gave a tried. And it's worked !
Now I'm wonder why they can do that ?
(Another similar is http://www.rac4sql.net/index.asp )

Subject: Please Help
Posted by: Thomas (view profile)
Posted on: Wednesday, May 23, 2007 at 1:33 PM
Message: Hallo i am an Rookie in this Statements. Please can you Help me to build this ??


first sorry for my English its not so. I have an problem with an table.
In this table there are 6 Collumns and this data
ID,Name,Date ,Code,Start,Stop
1 ,Max ,20.05.2007,TA ,00:00,00:30
1 ,Max ,20.05.2007,TA ,00:30,00:45
1 ,Max ,20.05.2007,TA ,05:00,05:30
1 ,Max ,20.05.2007,TO ,12:00,12:30

The ID can have max 42 different Codes a Day.

What i need is to fill a Second Table or an Select Statement to show this:
ID,Name,Date,Code,1,2,3,4,5,6,7,8....94 (each number for an intervall of 15 minutes)
1 ,Max ,20.05.2007,TA,0,0,1,1,.......
1 ,Max ,20.05.2007,TO,0,0,0,0,1,1,1.....

And so on.

The table or select must show
the count 1 for the intervall per day and code per id.

I hope all understand my question and what i want.
Thanks for Help
Regards
Thomas



Subject: Please Help
Posted by: Thomas (view profile)
Posted on: Wednesday, May 23, 2007 at 2:42 PM
Message: Hallo i am an Rookie in this Statements. Please can you Help me to build this ??


first sorry for my English its not so. I have an problem with an table.
In this table there are 6 Collumns and this data
ID,Name,Date ,Code,Start,Stop
1 ,Max ,20.05.2007,TA ,00:00,00:30
1 ,Max ,20.05.2007,TA ,00:30,00:45
1 ,Max ,20.05.2007,TA ,05:00,05:30
1 ,Max ,20.05.2007,TO ,12:00,12:30

The ID can have max 42 different Codes a Day.

What i need is to fill a Second Table or an Select Statement to show this:
ID,Name,Date,Code,1,2,3,4,5,6,7,8....94 (each number for an intervall of 15 minutes)
1 ,Max ,20.05.2007,TA,0,0,1,1,.......
1 ,Max ,20.05.2007,TO,0,0,0,0,1,1,1.....

And so on.

The table or select must show
the count 1 for the intervall per day and code per id.

I hope all understand my question and what i want.
Thanks for Help
Regards
Thomas



Subject: Large questionnaire crosstab
Posted by: KeithFletcher (view profile)
Posted on: Friday, May 25, 2007 at 3:19 AM
Message: For the 'Col level was too high' error, have a look inside the stored procedure. THere is an explanation inside that describes what is causing it and how to fix it. Unfortunately, the fix is a little too involved to explain here...

Subject: RE: Please Help
Posted by: KeithFletcher (view profile)
Posted on: Friday, May 25, 2007 at 3:22 AM
Message: Hi Thomas, What you want done is possible, if I understand your question. It might be easier if you send me a private message and explain exactly what you need done. Once I understand the problem, I'll publish the crosstab solution here.

Subject: Cross Tab Query in SQL-Server
Posted by: g3faa@se.com.sa (view profile)
Posted on: Friday, May 25, 2007 at 6:47 AM
Message: I have a problem using crosstab query in SQL-Server.

My Table structure is as follows

Table Name = BD_Tbl_PI_Budget_Actual

Structure is

fYear

fMonth

fOrgCode (organization Code)

fPI_No ( Performance Indicator Number)

fPI_B_Value (Budgeted Value)

fPI_A_Value (Actual Value)



Now my problem is when user want to view quarterly report he should see in a format like

Indicator wise Monthly value for Budgeted & Actual figure.



How could I achieve this, I read the crosstab procedure in this forum but I could not call it in a proper way.

Is there anyone to help?


Subject: Cross tab
Posted by: g3faa@se.com.sa (view profile)
Posted on: Friday, May 25, 2007 at 6:47 AM
Message: I have a problem using crosstab query in SQL-Server.

My Table structure is as follows

Table Name = BD_Tbl_PI_Budget_Actual

Structure is

fYear

fMonth

fOrgCode (organization Code)

fPI_No ( Performance Indicator Number)

fPI_B_Value (Budgeted Value)

fPI_A_Value (Actual Value)



Now my problem is when user want to view quarterly report he should see in a format like

Indicator wise Monthly value for Budgeted & Actual figure.



How could I achieve this, I read the crosstab procedure in this forum but I could not call it in a proper way.

Is there anyone to help?


Subject: RE: Cross Tab Query in SQL-Server
Posted by: KeithFletcher (view profile)
Posted on: Friday, May 25, 2007 at 7:22 AM
Message: Hi there,

Can you give me some sample data, so that I have a better idea of what you require?

Subject: Problem with CalcFieldType and nvarchar values...
Posted by: Brandon (view profile)
Posted on: Tuesday, May 29, 2007 at 11:34 AM
Message: I get the following function when executing the procedure: Syntax error converting the nvarchar value '2.2.18a' to a column of data type int.

EXEC sys_CrossTab
'Crosstab_Query',
'ScheduledDate',
'ScheduledDate',
'ScheduledDate',
'Version',
'CustomerName, Location, Description',
NULL,
'max',
0,
NULL,
1,
NULL,
NULL,
'CustomerName',
'int';

I am trying to print a version number instead of counts and sums in the grid, but I get the above error because it wants to do a calculation on the field and it is not int. I have tried making the CalcFieldType NULL, but then it tells me that ColTotal is invalid column.

Any ideas?

Subject: Problem with CalcFieldType and nvarchar values...
Posted by: Brandon (view profile)
Posted on: Tuesday, May 29, 2007 at 11:59 AM
Message: I get the following function when executing the procedure: Syntax error converting the nvarchar value '2.2.18a' to a column of data type int.

EXEC sys_CrossTab
'Crosstab_Query',
'ScheduledDate',
'ScheduledDate',
'ScheduledDate',
'Version',
'CustomerName, Location, Description',
NULL,
'max',
0,
NULL,
1,
NULL,
NULL,
'CustomerName',
'int';

I am trying to print a version number instead of counts and sums in the grid, but I get the above error because it wants to do a calculation on the field and it is not int. I have tried making the CalcFieldType NULL, but then it tells me that ColTotal is invalid column.

Any ideas?

Subject: RE: Problem with CalcFieldType and nvarchar values...
Posted by: KeithFletcher (view profile)
Posted on: Wednesday, May 30, 2007 at 3:20 AM
Message: Hi Brandon,

The very nature of the stored procedure dictates that the grid must be a calculation. I'm afraid that there's no way around that, and this is by design.
You'll have to set up your query manually.

What you might want to try is to run the above query on another column (other than version) that is an integer with the @Debug parameter set to 1. You can then cut-and-paste the generated SQL code, and manually adjust it to suit your requirements

Subject: ...only works with INT?
Posted by: Leif (not signed in)
Posted on: Friday, June 08, 2007 at 5:49 AM
Message: I guess I've the same situation as Brandon, where my data is not numeric. Setting @CalcOperation to COUNT seems to work, but setting it to MAX doesn't work - string to int conversion error.

Setting @CalcFieldType to 'VARCHAR(50)' yields 'The sum or average aggregate operation cannot take a varchar data type as an argument' ...but I'm doing a MAX? (with @RowTotals and @ColTotals set to null)

Otherwise, thanks for the proc - it will save us lots of time in other scenarios!

Subject: ...only works with INT?
Posted by: Leif (not signed in)
Posted on: Friday, June 08, 2007 at 7:15 AM
Message: I guess I've the same situation as Brandon, where my data is not numeric. Setting @CalcOperation to COUNT seems to work, but setting it to MAX doesn't work - string to int conversion error.

Setting @CalcFieldType to 'VARCHAR(50)' yields 'The sum or average aggregate operation cannot take a varchar data type as an argument' ...but I'm doing a MAX? (with @RowTotals and @ColTotals set to null)

Otherwise, thanks for the proc - it will save us lots of time in other scenarios!

Subject: Updated version
Posted by: KeithFletcher (view profile)
Posted on: Friday, July 06, 2007 at 4:31 AM
Message: I've made an update to the stored procedure. I noticed that it would terminate operation rather ungracefully should you feed it an empty source table. I've put a check in to counter that. You can find the updated copy at http://www.fletcher.co.za/SQLCrosstab/sys_CrossTab.sql

Subject: Columns to Rows
Posted by: dlgross (view profile)
Posted on: Tuesday, July 10, 2007 at 4:44 AM
Message: Keith, I have a table with Project, Start Date, End Date and Budget columns. I need a to calculate the total budget on a month by month basis. If project 1 is 10 months long, and project 2 is 8 months long, I'd like 10 rows and 8 rows. Any suggestions on how to accomplish this?
TIA
Dean

Subject: The crosstab worm never stops turning
Posted by: Steve (view profile)
Posted on: Tuesday, August 28, 2007 at 12:58 AM
Message: And here I thought we had solved all crosstab/pivoting problems on sql server with RAC. Is anyone listening? :-)

www.rac4sql.net

www.beyondsql.blogspot.com


Subject: sorry
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 21, 2008 at 4:13 AM
Message: not solving the problem

Subject: Exactly what I need
Posted by: Tom Hirt (not signed in)
Posted on: Friday, April 04, 2008 at 10:07 AM
Message: I'm a bit of a noob with t-sql but have a project that requires a dynamic cross tab. Trying to plug in my requirements and getting...
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't' does not match with a table name or alias name used in the query.

Is this msg board still monitored by Keith? I would love to get this working and share more detail if that would help. Thanks!!!

Subject: need help
Posted by: wawan (not signed in)
Posted on: Tuesday, May 27, 2008 at 4:26 AM
Message: town 1 2 3 4 > 4
new york 351 5 6 7 9
la 1051 120 22 6 7
alabama 31 32 11 2 3

have crosstab that the last field is more than or less then the last field. do you have the solve?

thx
http://wawan.forcium.net

Subject: Error
Posted by: JR` (not signed in)
Posted on: Thursday, May 29, 2008 at 12:44 PM
Message: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "P.AliasName" could not be bound.

Any Ideas?

Subject: Please Help
Posted by: JR (not signed in)
Posted on: Thursday, May 29, 2008 at 4:20 PM
Message: Does Any one use the @SourceFilter to pass parameters?

SELECT @PASSSTRING = 'optum_PGMetricsOnPkg.RequestID = ' & @RequestID

EXEC sys_CrossTab
'optum_PGMetricsOnPkg INNER JOIN
optum_Products ON optum_PGMetricsOnPkg.ProductID = optum_Products.ProductID INNER JOIN
optum_PGMetrics ON optum_PGMetricsOnPkg.PGMetricID = optum_PGMetrics.PGMetricID INNER JOIN
optum_PGClassification ON optum_PGMetrics.ClassificationID = optum_PGClassification.ClassificationID',
'ClassificationDescription', -- @ColFieldID
'ClassificationDescription', -- @ColFieldName
'AliasName', -- @ColFieldOrder
'ClassificationDescription', -- @CalcFieldName
'AliasName', -- @RowFieldNames
NULL, -- @TempTableName
'count', -- @CalcOperation
'1', -- @Debug
'@RequestID', -- @SourceFilter
'0', -- @NumColOrdering
'Total', -- @RowTotals
NULL, -- @ColTotals
'AliasName', -- @OrderBy
'int' -- @CalcFieldType


Returns Error:
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'optum_PGMetricsOnPkg.RequestID = ' to data type int.

Subject: Soory, I'm back...
Posted by: KeithFletcher (view profile)
Posted on: Sunday, July 20, 2008 at 6:37 AM
Message: Hi JR, wawan, and Tom,

Sorry, haven't been monitoring the comments for a while. If you still have problems, post the query you're using, as well as a short sample of the data you're trying to query and I'll try to help you out.

Regards,
Keith

Subject: Combining CrossTabs
Posted by: Anonymous (not signed in)
Posted on: Saturday, August 02, 2008 at 12:24 PM
Message: Hi Keith,
Thanks you very much for this SP... very nice!! :)

I am trying to combine cross tabs though.
I have these two tables coming back from the cross tabs:

FIRST CROSS TAB:
personid S-date1 S-date2 S-date3 ...

SECOND CROSS TAB:
personid D-date1 D-date2 D-date3 ....

I need to combine them to look like:
personid S-date1 D-date1 S-date2 D-date2 ...

Any ideas are very welcome :)
geo

Subject: Combining CrossTabs
Posted by: KeithFletcher (view profile)
Posted on: Thursday, August 07, 2008 at 2:40 AM
Message: Hi geo,

I would imagine that the easiest route would be to combine the source data of the your crosstabs first, then create a single cross tab on top of the combined sources.

As the crosstab generation is dynamic, it would be difficult (near impossible) to try combine the already completed crosstabs, and both rows and columsn may differ between the two.

Hope that makes sense???

Subject: Dealing with NULL values in the @CalcFieldName
Posted by: avin (view profile)
Posted on: Friday, September 05, 2008 at 1:51 PM
Message: Hi Everybody,

Can anybody please help me with this issue.

I have some null values in my calculated field and the @CalcFieldType is Float and the @CalcOperation is Sum.

The crosstab puts zero values whenever it encounters null. i want it to keep it as null or show a blank or a '.' anything but a 0.
This is because I have zero values in the crosstab which I don't want to confuse with null.

Thanks a lot for your help!

Subject: Dealing with NULL values in the @CalcFieldName
Posted by: avin (view profile)
Posted on: Friday, September 05, 2008 at 2:13 PM
Message: Hi Everybody,

Can anybody please help me with this issue.

I have some null values in my calculated field and the @CalcFieldType is Float and the @CalcOperation is Sum.

The crosstab puts zero values whenever it encounters null. i want it to keep it as null or show a blank or a '.' anything but a 0.
This is because I have zero values in the crosstab which I don't want to confuse with null.

Thanks a lot for your help!

Subject: RE: Dealing with NULL
Posted by: KeithFletcher (view profile)
Posted on: Wednesday, September 17, 2008 at 5:53 AM
Message: Hi Avin,

That's not going to be easily possible, and the crosstab values are aggregates, and you'll need to put some development effort into the SP to get it to keep the null's. Unfortunately, by design, the SP will treat all NULL's as zeros...

Subject: Outer join
Posted by: bjorn_i (view profile)
Posted on: Wednesday, November 26, 2008 at 8:21 AM
Message: I have a simple table (ContractEmployeeHours) with an EmployeeID, ContractID and Hours. When I run my query I only see those employees that actually have hours assigned to contracts. I tried using right outer join for the employee table; my source returns the correct number of rows, but when calling the crosstab, any employee without a record in the ContractEmployeeHOurs are missing.


Subject: Outer join
Posted by: bjorn_i (view profile)
Posted on: Wednesday, November 26, 2008 at 8:42 AM
Message: I have a simple table (ContractEmployeeHours) with an EmployeeID, ContractID and Hours. When I run my query I only see those employees that actually have hours assigned to contracts. I tried using right outer join for the employee table; my source returns the correct number of rows, but when calling the crosstab, any employee without a record in the ContractEmployeeHOurs are missing.


Subject: BEGINNER
Posted by: HELLO (view profile)
Posted on: Monday, May 04, 2009 at 12:09 PM
Message: I'M UNCLEAR AS TO WHERE TO PASTE EACH PIECE OF THE CODE. I PUT
"CREATE PROC [dbo].[sys_CrossTab]" INTO A STORED PROCEDURE" BUT WHERE TO I PUT THE "EXEC" PART AND WHERE WOULD I CALL THIS STORED PROCEDURE?

Subject: Thank you from the bottom of my CrossTab / Pivot Report!!!
Posted by: cmallain (view profile)
Posted on: Wednesday, October 14, 2009 at 8:16 PM
Message: Keith, You saved me countless hours of work and I'm on a very tight deadline. I GREAT appreciate this proc...it is awesome!!!

THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!THANK YOU! THANK YOU! THANK YOU!

Subject: Great Code, but I need help!
Posted by: tizziebird (view profile)
Posted on: Thursday, October 29, 2009 at 10:12 PM
Message: When I run the procedure for a query that returns a single row, everything is perfect. however, when I try to run it for a query that will return 2 or more rows (excluding total), I get this error: The column 'Aug-2009' was specified multiple times for 'X'.

When I look at the debug, it appears that it is trying to build the columns once for each row. for example, there are 3 distinct values for column I used as the @ColFieldID parameter: Jul-2009, Aug-2009, Sep_2009. The debug gives me this:

--6 Row totals, no col totals, no temp table
select [company nbr],[company name],[Cycle/Grace Days]
, sum([Aug-2009]) [Aug-2009]
, sum([Jul-2009]) [Jul-2009]
, sum([Sep-2009]) [Sep-2009]
, sum([Sep-2009]) [Sep-2009]
, sum([Jul-2009]) [Jul-2009]
, sum([Aug-2009]) [Aug-2009]
,
sum([Aug-2009])
+ sum([Jul-2009])
+ sum([Sep-2009])
+ sum([Sep-2009])
+ sum([Jul-2009])
+ sum([Aug-2009])
[Total]
from (select [company nbr], [company name], [Cycle/Grace Days]
, cast(case when MonthYear = 'Aug-2009' then isnull(sum(net_purch_vol), 0) else 0 end as numeric(18,2)) [Aug-2009]
, cast(case when MonthYear = 'Jul-2009' then isnull(sum(net_purch_vol), 0) else 0 end as numeric(18,2)) [Jul-2009]
, cast(case when MonthYear = 'Sep-2009' then isnull(sum(net_purch_vol), 0) else 0 end as numeric(18,2)) [Sep-2009]
, cast(case when MonthYear = 'Sep-2009' then isnull(sum(net_purch_vol), 0) else 0 end as numeric(18,2)) [Sep-2009]
, cast(case when MonthYear = 'Jul-2009' then isnull(sum(net_purch_vol), 0) else 0 end as numeric(18,2)) [Jul-2009]
, cast(case when MonthYear = 'Aug-2009' then isnull(sum(net_purch_vol), 0) else 0 end as numeric(18,2)) [Aug-2009]

Server: Msg 8156, Level 16, State 1, Line 1
The column 'Aug-2009' was specified multiple times for 'X'.

from v_OutputMain
where net_purch_vol is not null
and contractid = 1008 and proc_yr_mo between '200907' and '200909'
group by [company nbr], [company name], [Cycle/Grace Days], MonthYear) X
group by [company nbr],[company name],[Cycle/Grace Days]
order by [Company Nbr]


when I run this:
exec sys_crosstab
'v_OutputMain' ,
'MonthYear',
'MonthYear',
'[Company Nbr]',
'net_purch_vol',
'[company nbr], [company name], [Cycle/Grace Days]',
null,
'sum',
1,
'contractid = 1008 and proc_yr_mo between ''200907'' and ''200909''',
0,
'Total',
null, --'''TOTAL'','' '','' '', '' ''',
'[Company Nbr]',
'numeric(18,2)'


This procedure is exactly what I need as there is not always a pre-defined number of columns. I'm already missing my deadline and this procedure will save me lots of time! If you can help me to figure out what I'm doing wrong, I will be forever grateful.

Subject: tizziebird you may need some sort of a loop...
Posted by: cmallain (view profile)
Posted on: Friday, November 06, 2009 at 11:57 AM
Message: Hi tizziebird,

Not sure if this will help you but I had the same issue. What I tried to do was create a loop that could count the # of rows, that would eventually become columns, and force a 0 into each and generically label each but my problems came when I tried to have the field name of the temp column to be concat with a generic fieldname. I am not THAT good in SQL as used as a reporting tool yet to figure out how to do that so I ended up creating a temporary table with all values hardcoded as zero and then updated in the next step. Then nothing is missed. Yes, it's messy but I couldn't figure out a better way. Hopefully this gives you an idea. Good luck, Carol

Subject: Cross tab
Posted by: Cyber74 (view profile)
Posted on: Thursday, February 11, 2010 at 2:35 PM
Message: I have calendar table which has statdate, enddate, QTR ect. We are pulling Orders by QTR. I like to create a a cross-tab report which looks like this. If it possible to do this. Please help.....


FY10 FY09 FY08 FY07 FY09
Q1 5 15 17 18 40
Q2 0 2 22 21 10
Q3 55 11 10 8 5
Q4 5 1 2 0
Total

Subject: Creating a table from the output
Posted by: sallym (view profile)
Posted on: Tuesday, April 20, 2010 at 11:07 AM
Message: I think that this procedure is great but I need it more dynamic. I do not want to create a temporary table becuase the columns may increase do to new information being added to the database. Is there a way in the procedure to create an new parameter which is a name of a table and the procedures automatically creates a new table during execution.
Thanks so much

Subject: Creating a table from the output
Posted by: sallym (view profile)
Posted on: Tuesday, April 20, 2010 at 12:03 PM
Message: I think that this procedure is great but I need it more dynamic. I do not want to create a temporary table becuase the columns may increase do to new information being added to the database. Is there a way in the procedure to create an new parameter which is a name of a table and the procedures automatically creates a new table during execution.
Thanks so much

Subject: Creating a table from the output
Posted by: sallym (view profile)
Posted on: Tuesday, April 20, 2010 at 1:38 PM
Message: I think that this procedure is great but I need it more dynamic. I do not want to create a temporary table becuase the columns may increase do to new information being added to the database. Is there a way in the procedure to create an new parameter which is a name of a table and the procedures automatically creates a new table during execution.
Thanks so much

Subject: WHERE clause
Posted by: Ian_ (view profile)
Posted on: Thursday, June 10, 2010 at 7:05 AM
Message: Finally feeling very happy that I got this working on our system, I'm having trouble entering @SourceFilter to mimic the WHERE clause. No matter the syntax I always get no records returned. I suspect it's very simple once you know! Can anybody help please?

Subject: Keith Fletcher
Posted by: cmallain (view profile)
Posted on: Thursday, September 09, 2010 at 4:09 PM
Message: In regards to your WONDERFUL sys_crosstab program, how would I go about changing it so that my values that appear across the are in descending order? I'm not as sophisticated in my SQL programming as I'd like to be so any simple advice is GREATLY appreciated.
Carol

Subject: never mind...Kieth Fletcher...
Posted by: cmallain (view profile)
Posted on: Thursday, September 09, 2010 at 4:26 PM
Message: I figured it out... I simply added the descending keyword (desc) to the 2 lines of the ..."order by...@ColFieldOrder..." and it appears to be working.

Thanks anyway,
Carol

Subject: pls provide me SQL querry for below.
Posted by: kunal_ravi84 (view profile)
Posted on: Saturday, January 22, 2011 at 1:52 PM
Message: pls provide me SQL querry for below.

Table name : Citizen

Name | Country
--------|----------
Steve | Sweden
Matt | Denmark
george | England
Brad | Sweden
John | Denmark
Mark | England
Brain | sweden
jimmy | Denmark
pual | England
shaun |Sweden

output should be (or closest possible) as follows
Sweden | Denmark | England
----------------------------------------
Steve Matt George
Brad John Mark
Brain Jimmy Paul
Shaun NULL NULL

pls reply soon...

Subject: PostgreSQL
Posted by: lapichi2002 (view profile)
Posted on: Thursday, September 08, 2011 at 1:13 PM
Message: Excellent post!
Someone tried to do the same for PostgreSQL?

Subject: Thank you. Great Code.
Posted by: dhinvin (view profile)
Posted on: Wednesday, November 16, 2011 at 7:55 AM
Message: You saved lot of time.it is awesome!
Thank you,Thank you again.

Subject: WHERE clause
Posted by: rossrooker (view profile)
Posted on: Wednesday, February 20, 2013 at 1:51 PM
Message: I'm having trouble entering @SourceFilter to mimic the WHERE clause. No matter the syntax I always get the error
Incorrect syntax near '0'.

Here is the code I am using to set this parameter:

' tbl_company_1.cmp_vcr_codeid = ''DUNBAR'' ' -- @SourceFilter

Does anyone have a sample of how to set this parameter?

Subject: WHERE clause
Posted by: rossrooker (view profile)
Posted on: Wednesday, February 20, 2013 at 1:54 PM
Message: I'm having trouble entering @SourceFilter to mimic the WHERE clause. No matter the syntax I always get the error
Incorrect syntax near '0'.

Here is the code I am using to set this parameter:

' tbl_company_1.cmp_vcr_codeid = ''DUNBAR'' ' -- @SourceFilter

Does anyone have a sample of how to set this parameter?

Subject: WHERE clause
Posted by: rossrooker (view profile)
Posted on: Wednesday, February 20, 2013 at 5:35 PM
Message: I solved this. It was my error.

Subject: awesome for IIS log time series counts for uri stem!
Posted by: testmonger (view profile)
Posted on: Friday, March 28, 2014 at 1:12 PM
Message: Thank you Keith! I used this to generate a time series (15 minute increments) for csUriStems in a table with 2 million IIS log records. It shows the frequency of hits per page in 15 minute chunks throughout the day.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.