Crosstab Pivot-table Workbench

Robyn and Phil turn their attention to the bedrock of management reporting, the Pivot Table. Under Phil's 'wild man' influence, they end up with some rather radical ideas.



There comes a time with many Database Developers charged with doing management reports when the process of doing it properly gets very tedious. By ‘doing it properly’, I mean the ‘best practice’ of having to do the basic reporting in SQL and relying on a front-end application to do the presentation. This is particularly true where the management want simple aggregate reports, or ‘Pivot-table’ reports. Presentation is so closely tied with the data that splitting the process can sometimes lead to more problems than it solves. Of course, we have Reporting Services, Analysis Services and other external tools, but there are times when a simple solution based in TSQL has the upper hand.

Anyone who was weaned on Excel knows that these pivot tables are dead simple. You select your data, bang the button and with a bit of dragging and dropping, there it is. Why, they ask, is it so hard to get it out of the database? Why so hard to make changes?

What they want to see is something like this (using NorthWind so those stuck with SQL 2000 can join in)

No. Sales per year

1996 1997 1998 Total
Margaret Peacock 31 81 44 156
Janet Leverling 18 71 38 127
Nancy Davolio 26 55 42 123
Laura Callahan 19 54 31 104
Andrew Fuller 16 41 39 96
Robert King 11 36 25 72
Michael Suyama 15 33 19 67
Anne Dodsworth 5 19 19 43
Steven Buchanan 11 18 13 42
Sum 152 408 270 830

Now, you’ll notice that we’ve taken a bit of trouble to add some formatting. In the average business, they’re fussy about such things as the alignment of numbers and the clear delineation of totals, and summary lines. It also is easier on the eye. It is therefore handy to format pivot table reports in HTML. We can send them via email them, straight from SQL Server, all in their correct formatting, or put the reports on an intranet and update them daily or hourly. With the contents of this workbench we show you how it is done, and how easy it is to do, all without a .NET programmer in sight!

However, first things first, we must first show you how to do a crosstab, or pivot table in Transact SQL

Crosstabs and Pivot tables

The basic code to do the report is pretty simple.

You’ll notice that the years are hard-coded into the column headings, which are a time bomb waiting to happen. You’ll also realize that the all-important formatting is missing. The structure of the query seems slightly more complicated than necessary, but you’ll see why soon. The PIVOT operator in SQL Server 2005 makes it rather easier but we wanted to make this relevant to the SQL Server 2000 users too

For any sort of portable solution that will work on SQL Server 2000, dynamic SQL is the traditional solution. Basically, the stored procedure generates the tedious code and then executes it. Why, one wonders. This is because there are a number of tweaks that have to be made, such as the order of the columns, and rows. After all, wouldn’t someone want the report ordered by the number of sales of the salesman rather than just alphabetic order? The same basic query may generate a lot of different aggregations. Pretty soon, some sort of automation will be required.

Keith Fletcher contributed to Simple-Talk the ingenious but complex stored procedure that did cross-tabs in his excellent article Creating cross tab queries and pivot tables in SQL. Because we were awed by its grandeur, we didn’t initially want to add our own contribution. However, we had two another objectives, firstly to show how easy the technique can be, and also because we wanted to do more, encouraging you to try things out, and secondly, because we wanted to show how one might mark up the presentation of the crosstab.

Here is a stored procedure that does the trick, along with some examples using NorthWind. (if you are stuck with SQL Server 2000, make the Varchar(MAX)s into Varchar(8000) and don’t be too ambitious with the complexity of your crosstabs!)

You’ll see that this is a developer’s tool. It is easy to crash the procedure by putting in bad SQL. SQL Injectors would love it. No sir, this is a back-office report-generating tool. Let’s try it out:

414-pivot1.jpg

414-pivot2.jpg

HTML Crosstabs

Why bother with this when the Internet abounds with such Cross tab or pivot-table procedures? This is because we are going to take it one stage further so that, instead of just producing a resultset, we want to show how to produce HTML to produce the chart like the one at the beginning of the article.

To reproduce this…

value of orders per quarter

1st 2nd 3rd 4th Total
USA $81364.94 $50525.40 $58047.56 $55646.73 $245584.63
Germany $66823.20 $64681.22 $42550.91 $56229.29 $230284.62
Austria $32357.82 $37346.79 $17383.60 $40915.63 $128003.84
Brazil $47027.15 $12127.25 $22537.77 $25233.60 $106925.77
France $31085.27 $11225.97 $14407.11 $24639.96 $81358.31
UK $21302.05 $17061.85 $2292.70 $18314.72 $58971.32
Venezuela $21186.40 $11991.60 $12098.75 $11533.89 $56810.64
Sweden $13627.62 $18234.86 $8718.31 $13914.35 $54495.14
Canada $22746.94 $7094.48 $9225.70 $11129.18 $50196.30
Ireland $20500.54 $8291.50 $11376.50 $9811.36 $49979.90
Belgium $19410.67 $3728.48 $7740.70 $2945.00 $33824.85
Denmark $17049.90 $2007.79 $2127.25 $11476.08 $32661.02
Switzerland $4454.02 $10928.70 $7714.06 $8595.88 $31692.66
Mexico $3938.00 $12432.71 $4616.17 $2595.20 $23582.08
Finland $5532.80 $5791.20 $2884.41 $4601.64 $18810.05
Spain $7329.30 $1875.80 $4633.25 $4144.85 $17983.20
Italy $7082.09 $2836.10 $2484.37 $3367.60 $15770.16
Portugal $3335.79 $4311.20 $1519.24 $2306.14 $11472.37
Argentina $6684.10 $716.50 $0.00 $718.50 $8119.10
Norway $3354.40 $822.35 $500.00 $1058.40 $5735.15
Poland $587.50 $1277.60 $808.00 $858.85 $3531.95
Sum $436780.50 $285309.35 $233666.36 $310036.85 $1265793.06

…We need code like this (the first example in the body of the code was used to generate this Pivot-table)…

(n.b this is the SQL Server 2005 version. The SQL Server 2000 version is included with the files you can download at the bottom of the article)

You will have noticed a few things here.

  • The inputs are the same as the first stored procedure, spDynamicCrossTab. This means that you can try out your parameters in SSMS until you have things the way you want them and then you can just move to spDynamicHTMLCrossTab to concentrate on getting the presentation aspects as you want.
  • We have separated the style from the code. All presentation is in an inline style block. This means you can change the way the crosstab looks to your heart’s content.
  • with both stored procedures, you can specify the order of both the columns and rows precisely (it is always nice to have the months, or the days of the week in the correct order!
  • you can specify the units, either before(e.g. ‘£’ or ‘$’) or after (e.g ‘%’) the aggregate values
  • We provide you with an optional output variable so you can take the results and save it easily to a file, using the technique Phil described in his Blog ‘Using BCP to export the contents of MAX datatypes to a file’

You can change the appearance of the crosstab simply by changing the inline style. For example, this …

… will give you this

No. Sales per year

1996 1997 1998 Total
Margaret Peacock 31 81 44 156
Janet Leverling 18 71 38 127
Nancy Davolio 26 55 42 123
Laura Callahan 19 54 31 104
Andrew Fuller 16 41 39 96
Robert King 11 36 25 72
Michael Suyama 15 33 19 67
Anne Dodsworth 5 19 19 43
Steven Buchanan 11 18 13 42
Sum 152 408 270 830

Lastly, although there is a lot more one can say about these procedures and the tricks one can use, especially with the CSS, here is an illustration of the way one might save the results of your crosstab to an HTML file for your management reporting intranet site. This uses Phil’s technique taken from his blog entry Using BCP to export the contents of MAX datatypes to a file (Phil: Thanks for the plug, Robyn!)

So that’s it. We’ve enjoyed ourselves trying things out, and we’ve been surprised how far we can take the dynamic creation of pivot tables. We suggest you take the workshop and explore the ideas. If you discover anything interesting, we’d love to hear your comments! We’d particularly like to hear of interesting CSS layouts, though displaying them on Simple-Talk will be very difficult.

Downloads

Tags: , , ,

  • 82583 views

  • Rate
    [Total: 88    Average: 4.3/5]
  • Fahim

    CrossTabs & Pivots In SQL 2005
    How does one go about
    constructing a dynamically generated crosstab resultset using the Pivot function in SQL 2005.!?

    I have data that spans various years, but the range is flexible depending on the object on whom the data is currently being aggregated.

    I.E. Product sales over the lifetime of availability, ranging over the various product types. I don’t want to write a separate function for each product type (since this is the dynamic content), rather just generate the year-sales column on the fly.

    RSVP feemurk@gmail.com

  • Greg Obleshchuk

    CrossTabs & Pivots In SQL 2005 & SQL 2000
    While this is a good article a lot of time an effort goes into creating a dynamic pivot SP. I don’t know why people bother. GeckoWare Australia (my company) has been selling(online) our Crosstab/Pivot Extended Stored Procedure for SQL 2000 and SQL 2005 for the past 5 years. This si a great tool and one that can turn the nightmare of created pivots into a 2 minute process. Our tool even support multiple value and multi crosstab columns all for US$150 per server instance. It can be downloaded at http://www.geckoware.com.au

    Great article but a lot of time wasted.

    Kind regards
    Greg Obleshchuk
    Director
    GeckoWare Australia

  • JJ

    Great Article & GeckoWare is down-under
    Great Article – Thanks
    I wish the Pivot feature in 2005 was more Dynamic 🙁

    Here are some more great articles on dynamic cross-tabs:
    http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

    http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

    http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

    http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

    As for Geckoware = Sorry Mate but I went to your site and tried to run the your online demo but it went to never never land and did not return

    -jj

  • Phil Factor

    Re: GeckoWare & JJ
    Thanks for the compliment. We really appreciate that.

    Perhaps we didn’t emphasise the fact well enough but Greg of Geckoware may not have spotted that we were aiming to go beyond doing a pivot, in order produce HTML presentation-quality reports of the results. I may have missed something, but I don’t think that Geckoware does this!

    Other than the bitmaps of the SMSS results grid, The various illustrations in the article are done by pasting the HTML output from the code directly into the article. No trickery or bitmaps. This is the actual output.

    Apologies to JJ. Robyn says in the article ‘Why bother with this when the Internet abounds with such Cross tab or pivot-table procedures?’. We needed to do this so that you end up with two procedures, one that produces a plain crosstab that produces a resultset, and another one with the same parameters for doing the result as an XHTML fragment, for pasting into reports or HTML files.

    With the two procedures, you can use the first to develop reports for the second. It saves quite a bit of time.

  • Trevor Scurr

    HTML or XML
    Appreciate the article, but I’m not personally sure about the maintainability of a stored procedure containing embedded HTML tags. Has anyone put any thought into a pivot table that also uses SQLXML to generate an XML data set that can then be styled into HTML using xslt? I know you could argue that this is XHTML and hence XML but there is still a world of difference between embedding tags and letting SQLXML do the leg work. I’m about to go on vacation otherwise I’d have a play here myself – maybe when I come back!

    Regards
    Trevor

  • Trevor Scurr

    HTML or XML
    Hmmm just had a crashing realisation – of course SQLXML doesnt support grouping constructs which is the raison d’etre of pivot tables. Guess some further thinking is required….

  • Anonymous

    HTML or XML
    Sorry – me again.. you can do this with XML EXPLICIT. Taking the first query and changing to

     

     

     

    gives a reasonable XML set although I probably would go on to play with the row ordering. For the future I think I will look at the generalised pivot query

  • Robyn Page

    Re: HTML or XML
    We initially decided that we’d go the HTML/XHTML route purely because we wanted something that the SQL Server 2000 people could join in with.

    Subsequently Phil decided it would round off the article quite nicely to show off how simple it was to use FOR XML to produce XHTML fragments containing the pivot table. He’s still trying, so how about a Simple-Talk goodie-bag for the best solution? (Phil loves the cute Red-Gate Memory stick which is included)

  • Ben

    Great Article
    This is a wonderful topic. Pivot tables are one of the best data mining capabilities in SQL.

    Any time someone gets away from a series of hard coded CASE statements you really extend the functionality of your code. I have written a number of articles on the same theme using pivot queries and a table with date ranges to generate a rolling window of time. You may enjoy looking at some of those at http://www.sswug.org.

    Cheers,

    Ben Taylor

  • Trevor Scurr

    Re: HTML or XML
    Just for the record the above query works on SQL Server 2000 with the SQL XML 3.0 library. In common with many of your readers I’m sure we still find our clients primarily using 2000. And SQL Server 2008 is looming….!

  • Noel Cruz

    Revealing My Ignorance…
    In an attempt to implement (create) the proc that produces the HTML output, I get an error on the following piece of code:

    INSERT INTO @StringTable(string,waste)
    EXECUTE (@DataRows)

    The error states, “Msg 197, Level 15, State 1, Procedure spDynamicHTMLCrossTab, Line 164
    EXECUTE cannot be used as a source when inserting into a table variable.”

    Any suggestions?

  • DuWayne Willett

    This is what Analysis Services is for
    Well, you can do extended calisthenics to do this type of PivotTable reporting using SQL, but SQL is fundamentally not the optimal tool for the job.

    Dimensionally-modeled databases/cubes do this type of reporting very easily.
    A cleaner and simpler way of doing all the above is to use Analysis Services (or another OLAP engine) to construct a cube, with queries written in MDX (multidimensional expressions), not SQL.
    Fortunately the end-user tools (including Excel PivotTables, or HTML equivalents), produce the MDX themselves automatically when the user drags-and-drops selections in the PivotTable. You can write your own MDX queries if you want to, but it’s not required.

  • dterrie

    a few additional wrinkles
    At a previous job, we had a .net app that generated HTML pages, complete with tables similarly formatted using tablestyle css. We had a few additional features: colspans in header rows, and oddrow and evenrow background color options. A sproc unioned these rows into it’s output along with a Format column, not used in the table, that was populated with a row type case statement. We then iterated through the rows and columns to set the styles and content.

  • Phil Factor

    Re Revealing my ignorance
    I’ve just popped a SQL Server 2000 version of the second stored procedure in the ‘speechbox’ at the top of the article with the other two files. In SQL Server 2000 you have to use a temporary table intead of a table variable. You also can’t use Varchar(MAX) and there is a bug in the GROUP BY which gives an error with perfectly correct syntax!

  • Noel Cruz

    Thanks!
    Thanks a lot, Phil!

    Before seeing this, I did change the VARCHAR(MAX) appropriately for SQL Server 2000, and I was attempting to fix the GROUP BY stuff as well.

    Anyway, thanks for the help!

  • Mark Hill

    Edits for SQL Server 2000
    Great article, but I have SQL Server 2000, and your code includes some SQL Server 2005 idioms that I had to change. In addition to the minor VARCHAR(8000) for VARCHAR(MAX) replacement that you mentioned, I also needed to replace NVARCHAR(MAX) with NVARCHAR(4000). But the most serious change is to circumvent SQL Server 2000’s inability to ‘GROUP BY ‘+@ColValue when the @ColValue is used in an expression. (It complains that “Column ‘…’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”) I accomplished this by putting the grouping in a subquery. In spDynamicCrossTab, I changed then “SELECT @Command=” statement to:

    SELECT @Command=’select @SQL=coalesce(@SQL,”SELECT
    [‘+@Title+’]=case when row is null then ””Sum”” else convert(Varchar(80),[row]) end ,
    ”)+
    ”[”+convert(varchar(100),
    _colvalue_)+”] =sum( CASE col WHEN ”””+convert(varchar(100),
    _colvalue_)+””” THEN data else 0 END ),
    ” FROM (SELECT TOP 100 PERCENT ‘+@ColValue+’ AS _colvalue_ ‘+@FromExpression+’
    GROUP BY ‘+@ColValue+’
    order by max(‘+@ColorderValue+’)) subq’

  • jenniebee

    more flexibility possible
    This uses XML to do the pivoting, no dynamic sql or case statements needed:

  • jenniebee

    explanation
    The character restriction limited the prettifying.

    In SQL2K5, you don’t have to pivot or use case statements to make the XML pivot because the practical difference between 1 row by 5 columns, each labeled ‘td’ and 5 rows with one column labeled ‘td’ is nil. Instead of using the Order Year to form the basis for dynamically generated columns, you can simply take a distinct of the order years to generate a list of fields labeled <th>, then use the OrderYears to ensure that your data fields are presented in the same order.

    The code is a little more off the beaten path, but it isn’t any more difficult to maintain (when properly documented) than a dynamic SQL statement, and the only hard coded value in it is the number of years presented as columns.

  • Phil Factor

    Re: More flexibility possible
    Remarkable! This gives exactly the same XHTML code as the spDynamicHTMLCrossTab, and passes through HTMLTidy without a word of reproach. I’d begun to think it wasn’t possible.

    The importance of this technique could be far reaching as it shows how table-based XHTML fragments can be generated directly from SQL without string-manipulation trickery. Attach a CSS style-sheet and you then have presentation of data.

  • DuWayne Willett

    This is what Analysis Services is for
    Well, you can do extended calisthenics to do this type of PivotTable reporting using SQL, but SQL is fundamentally not the optimal tool for the job.

    Dimensionally-modeled databases/cubes do this type of reporting very easily.
    A cleaner and simpler way of doing all the above is to use Analysis Services (or another OLAP engine) to construct a cube, with queries written in MDX (multidimensional expressions), not SQL.
    Fortunately the end-user tools (including Excel PivotTables, or HTML equivalents), produce the MDX themselves automatically when the user drags-and-drops selections in the PivotTable. You can write your own MDX queries if you want to, but it’s not required.

  • jenniebee

    ty
    What I like about it is that it isn’t just for reporting; I have some schema elements that the front end wants to display horizontally, but storing each of their “attributes” in a different column leads very quickly with us to scalability issues (“add a column” has been a very frequent Change Request around here). That XML approach has allowed me pivot what they want to be horizontal into a vertical storage space and solves my scaling problems.

  • LukCAD

    thank you
    it is good template for creating of crosstables. Indeed it is easy to use without any changements and it is the case why i would like to send my regards. It is bright procedure and bright explanation how to use it. I remembered that I have one example from internet (that i found like yours) where i never changed any code to use it in my solutions, i put it here, to show how it is useful to have into your own repositary good examples: <code>
    — converts string with comma delimiters in table
    ALTER FUNCTION [dbo].[StringToTable]
    (
    @string varchar(8000)
    )
    RETURNS @Values TABLE (id INT )
    AS
    BEGIN
    DECLARE @posPrev INT
    DECLARE @pos INT
    DECLARE @posMax INT
    DECLARE @Token VARCHAR(8000)
    DECLARE @value INT

    SET @pos = 0
    SET @posMax = LEN(@string)
    WHILE (@pos < @posMax)
    BEGIN
    SET @posPrev = @pos + 1
    SET @pos = CHARINDEX(‘,’, @string, @posPrev)
    IF (@pos = 0)
    SET @pos = @posMax + 1
    IF (@pos = @posPrev)
    CONTINUE
    SET @Token = LTRIM(RTRIM(SUBSTRING( @string, @posPrev, @pos – @posPrev)))
    IF ((LEN(@Token) = 0) OR (ISNUMERIC(@Token) = 0))
    CONTINUE
    SET @value = CONVERT(INT, @Token, 0)
    INSERT @Values SELECT @value
    END
    SET @string = ”
    RETURN
    END
    </code>

  • Dave

    Great Article
    Thanks this article was very helpful…. just what I was looking for to help expand my selftaught skillset. Although I suggest you take the author Robin Page’s photo off the article… that hot woman is distracting dammit!!

  • Anonymous

    There are crosstabs and then there are crosstabs.
    We’ve been offering the RAC utility for some time. We still haven’t seen anything comparable. All the functionality you could want for less than $.50 a parameter 🙂 You can check us out @
    http://www.rac4sql.net

  • Jereme Guenther

    creating pivot table
    Very nice. I didn’t need the summation, instead I needed to simply turn a table on its side making one of the columns to new headers.

    DECLARE @SQL varchar(8000)

    Create Table #tmp_pivot(
    id int identity(1,1)
    )

    SET @SQL=”
    SELECT @SQL = @SQL + ‘ALTER TABLE #tmp_pivot ADD [‘ + Replace(sq.newColumnName,””,”””) + ‘] varchar(1000) ‘
    From mytable sq

    Basically I created a new temporary table, then used dynamic SQL to add all the needed columns to it. Next I used another dynamic SQL select statement to populate the temporary table.

    It definitely isn’t the prettiest solution in the world, and really leaves me longing for SQL2005, however it does work.

    The most important thing to realize to make this work is that temporary tables can be used and modified between regular and dynamic SQL as long as they are not created in the dynamic SQL.

    Now that I have spend the time doing this I am going to take a look at that reply who says they have been selling an automated sproc for this. I am going to be really happy, though slightly depressed over the time I waisted, if their offering works.

  • Anonymous

    sql 2005 pivote
    why don’t just use sql server 2005 pivote stuff

  • Reinder

    Execute the HTML crosstab
    When i try to execute the DynamicHTMLCrossTab there is a error :

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value ‘Sum’ to data type int.

    DECLARE @HTMLString VARCHAR(MAX)
    EXECUTE usproc_DynamicHTMLCrossTab
    @colValue='[dbo].[Fn_ConvertDateShort](Datum)’,
    @rowValue=’PLID’,
    @Aggregate= ‘sum(MLDAantal)’,
    @FromExpression=’FROM #VAKANTIE’,
    @ColOrderValue=’Datum’,
    @Title =’Medewerker’,
    @Output=@HTMLString output
    SELECT @HTMLString=
    ‘<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
    “http://www.w3.org/TR/html4/loose.dtd”>
    <html>
    <head><title>Customers orders per month</title></head>
    <body>’+@HTMLString+'</body>’
    EXECUTE sproc_SaveTextToFile @HTMLString, ‘C:test.html’

    Can anyone tell me what I do wrong?

  • Anonymous

    wonderfull same code
    Thank you very much for this wonderfull same code!

  • Anonymous

    Questions
    Seems to work but I have a couple questions.

    I’m using SQL 2000.

    1. records with Null value in the row heading column show up in the result with ‘Sum’ in the heading

    2. when running the html sp I get 2 results.
    1st one is ‘Select @Headinglines=coalesce(@headinglines,'<div id=”MyCrosstab……….

    2nd result is the html for the crosstab

    what is the 1st result for?

    Thanks

  • Anonymous

    Questions
    Seems to work but I have a couple questions.

    I’m using SQL 2000.

    1. records with Null value in the row heading column show up in the result with ‘Sum’ in the heading

    2. when running the html sp I get 2 results.
    1st one is ‘Select @Headinglines=coalesce(@headinglines,'<div id=”MyCrosstab……….

    2nd result is the html for the crosstab

    what is the 1st result for?

    Thanks

  • Anonymous

    I NEED QUERY IF YOU PLEASE
    Table : T1

    ID DESC V1 V2 V3
    1 D1 1 2 3
    1 D2 1 2 3
    1 D3 1 2 3
    2 D1 1 2 3
    2 D2 1 2 3
    2 D3 1 2 3
    3 D1 1 2 3
    3 D2 1 2 3
    3 D3 1 2 3
    4 D1 1 2 3
    4 D2 1 2 3
    4 D3 1 2 3

    HOW TO DO THIS REPORT:
    D1 D2 D3
    ID V1 V2 V3 V1 V2 V3 V1 V2 V3
    1 1 2 3 1 2 3 1 2 3
    2 1 2 3 1 2 3 1 2 3
    3 1 2 3 1 2 3 1 2 3
    4 1 2 3 1 2 3 1 2 3

  • Anonymous

    I NEED QUERY IF YOU PLEASE
    Table : T1

    ID DESC V1 V2 V3
    1 D1 1 2 3
    1 D2 1 2 3
    1 D3 1 2 3
    2 D1 1 2 3
    2 D2 1 2 3
    2 D3 1 2 3
    3 D1 1 2 3
    3 D2 1 2 3
    3 D3 1 2 3
    4 D1 1 2 3
    4 D2 1 2 3
    4 D3 1 2 3

    HOW TO DO THIS REPORT:
    D1 D2 D3
    ID V1 V2 V3 V1 V2 V3 V1 V2 V3
    1 1 2 3 1 2 3 1 2 3
    2 1 2 3 1 2 3 1 2 3
    3 1 2 3 1 2 3 1 2 3
    4 1 2 3 1 2 3 1 2 3

  • Jim

    name
    You write a SQL column and your name is Phil Factor? Is that real?

  • Anonymous

    Cross Tab
    good job I like it

  • dgaylor

    how to add another column
    Hi, I am not sure how to do this but is it possible to add another column to the report, such as for the ‘No. Sales per year’ example, maybe adding the postal code from the employees table? Would it be easy to change the sp to accomodate this? Thanks.

    • Phil Factor

      Re: how to add another column
      Yes, it is very easy to do. In the example, you are getting firstname+lastname, but you can add anything else from the result that you are specifying and it should work.

      • dgaylor

        Re: how to add another column
        So I can have 2 columns separted by comma?

  • y43a

    Turn Off Sum Row
    Hi,

    How do I modify spDynamicCrossTab to not display the bottom row ‘Sum’?

    Thanks

  • rickyv

    Top 10
    Is there any way to only select the top 10 rows?

    Thanks