InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 18:04:37 GMT20http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]vadnal.vijaykumar (7/26/2010)[/b][hr]Hi JeffThanks for the informative article.But with this same posting, I am currently working on some Reports using ASP.NET and SQL Server. I want to populate data from 3 tables. Out of which 1 is used for populating Table schema for report layout which could contains a transactional data for a particular year. Then second could contains Number's which is being used as first column in the report and with this Number reference I am gonna call all the Codes Amount and Count from Code table i.e. my last table.For schema generation I have written Cursor, so that it will populate transaction data and dynamically added those rows in Create/Alter table script.For next I have written simple SP to return data table's 1 is for Number and another for Codes.Then I am looping through 1 table(Number) then in nested loop for Codes I have passed both Number and Code to get my Amount and Count from ASP.NET coding.All is fine but, I want to do this from the SQL Server directly not by using ASP.NET, because I have measured performance it is populating data with least performance.Thanks,Vijay[/quote]Hi Vijay,I have two recommendations here... my first would be that only those folks who have ever clicked on the article will ever see your posting above. Since I don't always have the time to post an answer, my recommendation would be for you to post your question on the appropriate (2000, 2005, or 2008) forum. Before you do that, my second recommendation would be to read the article at the first link in my signature line below so you stand the chance of getting really good answers.I would also keep in mind that people are going to ask you what you've done to try to resolve your own problem in the form of posted code.Mon, 26 Jul 2010 06:11:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi JeffThanks for the informative article.But with this same posting, I am currently working on some Reports using ASP.NET and SQL Server. I want to populate data from 3 tables. Out of which 1 is used for populating Table schema for report layout which could contains a transactional data for a particular year. Then second could contains Number's which is being used as first column in the report and with this Number reference I am gonna call all the Codes Amount and Count from Code table i.e. my last table.For schema generation I have written Cursor, so that it will populate transaction data and dynamically added those rows in Create/Alter table script.For next I have written simple SP to return data table's 1 is for Number and another for Codes.Then I am looping through 1 table(Number) then in nested loop for Codes I have passed both Number and Code to get my Amount and Count from ASP.NET coding.All is fine but, I want to do this from the SQL Server directly not by using ASP.NET, because I have measured performance it is populating data with least performance.Thanks,VijayMon, 26 Jul 2010 01:17:45 GMTvadnal.vijaykumarhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxI have been discussing the WITH CUBE, CUBE() and Excel Pivot Table functionality with Microsoft's SQL Server and Excel Teams. Here is the highlights of the discussion to date:WITH CUBE is deprecated but works with 80, 90 and 100CUBE() will be supported moving forward but is only works with 100The discussion is continuing on whether Excel Pivot Tables will fully support a CUBE (NULL subtotal rows) properly when the CUBE is returned from a TSQL CUBE() function.Thu, 08 Jul 2010 07:28:20 GMTeq2homehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxOutstanding. Always makes my day when someone can apply something they learned from one of my articles. Thanks for the great feedback.On the other stuff... I'm one of those that wish MS would get things together a bit better across their products. I know I'll get some arguments on this but using Excel as a reporting tool for SQL Server is something that a whole lot of people correctly need to do. You'd think that MS would make it a little easier to do even via T-SQL sans SSRS.Thu, 01 Jul 2010 06:44:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxI reported the depreciation of the WITH CUBE functionality.This is an interesting case that requires more investigation. Microsoft's Excel 2007 does not respect the NULL rows for subtotaling in the returned cursor using Microsoft Query into an Excel Pivot table. (By the way, kudos to Microsoft for the GREAT improvement in Excel 2007's handling of Pivot integration with SQL Server...more on this at a later time.)When Excel grabs the Cube and puts it into Excel (again great job on the automation but Stored Procedures are not straight forward) it doubles all counts because it turns null into a row in the cube and calls it (blank). Ok, so the solution is busy work (turn off the (blank) fields in all row groups. It is a bit clumsy but it works. The bigger question is shouldn't Microsoft take advantage of 2008 functionality and automatically adjust for the NULL (blank) rows coming from SQL Server 2008 in Excel? This was the question I posed to the Microsoft Excel 2007 Team. The response from the Microsoft support team was "don't use this feature because it was being depreciated." Ok, fine. Notice that the answer was not use the CUBE() feature. The reason it was not use the CUBE() feature was that the Excel Team had not caught up with the SQL Team.So, with the CUBE() or WITH CUBE (much appreciation to the submission that cleared up the confusion on support for the 2008 TSL CUBE) functionality we are back at the same question "when will Microsoft fully support (automate) the CUBE functionality in Excel 2007 or higher? Again, my hat is off to Microsoft's efforts to integrate Excel and SQL Server but I want (and my customers surely do) better automation support between Excel and SQL Server. The last time I checked both products were made by the same company and that company. Cheers and Jeff again THANKS for your enlightening original article. I WoWed some customers this weekend based on some of the fundamentals your article provided.Wed, 30 Jun 2010 19:09:47 GMTeq2homehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxAh... I feel much better now. I had seen the BOL info before but thought someone had come up with a new bit of info where they weren't replacing the deprecated forms of WITH ROLLUP and CUBE. I was [i]really [/i]concerned because I use the two clauses on a regular basis.Sat, 26 Jun 2010 06:48:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Jeff Moden (6/25/2010)[/b][hr][quote][b]eq2home (6/24/2010)[/b][hr]Here is what I found out today from TechNet (Notice "the will be removed" language:WITH CUBE This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified. WITH ROLLUP This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.[/quote]Have you got a URL for that? If they actually remove it, I'm going to be pretty well ticked off because it's incredibly useful and very fast.[/quote]It's in the [url=http://msdn.microsoft.com/en-us/library/ms177673.aspx][u]2008 BOL[/u][/url]. It looks like "WITH CUBE" is being replace with CUBE(), and "WITH ROLLUP" is being replaced with "ROLLUP()", though it doesn't seem to specifically state that.Fri, 25 Jun 2010 08:11:43 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]eq2home (6/24/2010)[/b][hr]Here is what I found out today from TechNet (Notice "the will be removed" language:WITH CUBE This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified. WITH ROLLUP This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.[/quote]Have you got a URL for that? If they actually remove it, I'm going to be pretty well ticked off because it's incredibly useful and very fast.Fri, 25 Jun 2010 07:13:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHere is what I found out today from TechNet (Notice "the will be removed" language:WITH CUBE This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. A GROUP BY summary row is returned for every possible combination of group and subgroup in the result set. Use the GROUPING function to determine whether null values in the result set are GROUP BY summary values.The number of summary rows in the result set is determined by the number of columns included in the GROUP BY clause. Because CUBE returns every possible combination of group and subgroup, the number of rows is the same, regardless of the order in which the grouping columns are specified. WITH ROLLUP This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest. The group hierarchy is determined by the order in which the grouping columns are specified. Changing the order of the grouping columns can affect the number of rows produced in the result set.Thu, 24 Jun 2010 11:11:24 GMTeq2homehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]eq2home (6/11/2010)[/b][hr]Super explanation and it clarifies and identifies a simpler and faster approach to building crosstabs. My hat is tipped to you Jeff - Pick clarity of thought, writing ability and an eye for logic and you are lucky to find one existing in a single person. It is extraordinary to find all three in a single individual. You saved me mucho time, frustration and efforts - Thanks.[/quote]Wow. Thanks for the awesome compliments. :blush: Glad I could help.Fri, 11 Jun 2010 09:19:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxSuper explanation and it clarifies and identifies a simpler and faster approach to building crosstabs. My hat is tipped to you Jeff - Pick clarity of thought, writing ability and an eye for logic and you are lucky to find one existing in a single person. It is extraordinary to find all three in a single individual. You saved me mucho time, frustration and efforts - Thanks.Fri, 11 Jun 2010 08:42:05 GMTeq2homehttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThank you for the incredible feedback, Pac123. It looks like you've certainly hit all the high points. :-)Sun, 23 May 2010 21:42:07 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi Jeff,Fantastic article, i have to create a Dynamic cross tabbed report, where the column tabs are each week of a year not monthly!! :) !!!!this article is exactly what i was looking for, something that creates those dynamic cross tabs without ANY cursors, temp tables or global temp tables...I was able to create the weekly tabs with some minor tweaking to your code ...but i must say in addition to learning how to create a cross tabs on the fly , i picked up a few other pointers-1] Creating the 'test' table and tally table- something that i will use to create test data in future..2] Some useful pointers for query performance, like using a select to assign a variable..3] Using the cube and rollup functions4]Using a select and coalesce to concatenate multiple columnsBut most importantly- how to break up the problem statement into little chunks--- i.e. creating the 'static' portion first and then 'converting ' into the dynamic portion using variables...much appreciated and i hope to keep learning more..!!!!Sun, 23 May 2010 20:11:32 GMTPac123http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]ziangij (5/7/2010)[/b][hr]great article... thank you :-)[/quote]You're welcome. Thanks for both the visit and the feedback.Fri, 07 May 2010 06:32:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxgreat article... thank you :-)Fri, 07 May 2010 03:54:38 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Doug Bass (5/4/2010)[/b][hr]Another [b]excellent[/b] article, Jeff. I liked it as much as, or better than, part one. I especially liked your usage of concatenation to generate the columns in the SELECT clause. Does this trick work in other DBMS's, do you know? Thanks so much for the valuable contribution![/quote]Thanks for the feedback Doug,Overlaying variables in a single SELECT in fact DOESN'T work in a lot of other RDBMSs. It was always one of the frustrations I suffered when I had to do work with Oracle. It does, however, work with SyBase which is also based on the "Rushmore" RDBMS engine and is where SQL Server got its roots from.Do be advised that that type of concatenation can have it's problems. I covered some of the problems in the following article along with some of the remediations...[url]http://www.sqlservercentral.com/articles/Test+Data/61572/[/url]There's also a speedier way to do the concatenation using FOR XML PATH('') starting in SQL Server 2005. That method is briefly covered at the end of that same concatenation article.Tue, 04 May 2010 11:54:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxAnother [b]excellent[/b] article, Jeff. I liked it as much as, or better than, part one. I especially liked your usage of concatenation to generate the columns in the SELECT clause. Does this trick work in other DBMS's, do you know? Thanks so much for the valuable contribution!Tue, 04 May 2010 11:36:23 GMTDoug Basshttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThanks for the feedback Jason and Peter.Rumor has it that Steve is working on making a couple of training "movies" for the two articles in this series. He's pretty darned good at such things. I can't wait to see what he comes up with.Mon, 03 May 2010 06:42:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxGood one Jeff.Mon, 03 May 2010 03:04:09 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxNice article Jeff. Nice to see it republished so we can review it again.Fri, 30 Apr 2010 12:14:09 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Dennis Wagner-347763 (4/30/2010)[/b][hr]Super article Jeff -- as always. Thanks especially for writing something that works in SQL 2000 (until you hit the 4000 char limit). A number of us out there are still supporting some SQL 2000 systems.Here is one for your "Super Ninja" -- use the GROUPING function to help with those gnarly ROLLUPS and CUBES.Select part:SELECT CASE GROUPING(Field1) WHEN 1 THEN 'Total' ELSE Field1 END -- puts "Total" in when rolled upOrder by part:ORDER BY GROUPING(Field1) ASC, Field1 (sorts the totals to the bottom, change ASC to DESC to sort them to the top)And finally when using WITH CUBE because you want some of the other dimensions but not all of them, use the HAVING clause to eliminate some of themHAVING GROUPING(Field3) = 0 -- will not roll up this field into a totalAND GROUPING(Field4) = GROUPING(Field5) -- rolls these up together, which is perfect when Field4 is the ID and Field5 is the description and you don't want to roll them up separatelyThis works with WITH ROLLUP too![/quote]I agree... WITH ROLLUP and CUBE add little time to the overall duration and GROUPING makes it understandable. Heh... without it, most folks just can't get their arms around what all the NULLs mean especially when using WITH CUBE.Thanks for the feedback, Dennis.Fri, 30 Apr 2010 07:15:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Madhivanan-208264 (4/30/2010)[/b][hr]When I visited belution forum, I used to think you were the moderator or site owner and I came to know about you only from that forum :-)But what happened to it? I wanted to ask about it to you sometimes back[/quote]I'm not sure what happened to the Belution forum. One day, it just stopped working. I did a search for info about it on the web a while back and there was some little news that someone was thinking of selling it. Not sure what really happened, though.Fri, 30 Apr 2010 07:11:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxSuper article Jeff -- as always. Thanks especially for writing something that works in SQL 2000 (until you hit the 4000 char limit). A number of us out there are still supporting some SQL 2000 systems.Here is one for your "Super Ninja" -- use the GROUPING function to help with those gnarly ROLLUPS and CUBES.Select part:SELECT CASE GROUPING(Field1) WHEN 1 THEN 'Total' ELSE Field1 END -- puts "Total" in when rolled upOrder by part:ORDER BY GROUPING(Field1) ASC, Field1 (sorts the totals to the bottom, change ASC to DESC to sort them to the top)And finally when using WITH CUBE because you want some of the other dimensions but not all of them, use the HAVING clause to eliminate some of themHAVING GROUPING(Field3) = 0 -- will not roll up this field into a totalAND GROUPING(Field4) = GROUPING(Field5) -- rolls these up together, which is perfect when Field4 is the ID and Field5 is the description and you don't want to roll them up separatelyThis works with WITH ROLLUP too!Fri, 30 Apr 2010 06:33:40 GMTDennis Wagner-347763http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Jeff Moden (2/25/2010)[/b][hr][quote][b]Mike M - DBA2B (2/25/2010)[/b][hr]Ha!Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. ;-)Regards,Mike M[/quote]Oh my... that WAS a long time ago. That was on the "BELUTION" forum which has been offline for many years... probably about 6 just as you mention. Glad to see you around![/quote]Good Article JeffHere are mineSQL Server 2000 - [url]http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx[/url]SQL Server 2005 - [url]http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx[/url]When I visited belution forum, I used to think you were the moderator or site owner and I came to know about you only from that forum :-)But what happened to it? I wanted to ask about it to you sometimes backFri, 30 Apr 2010 03:14:50 GMTMadhivanan-208264http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Mike M - DBA2B (2/25/2010)[/b][hr]Ha!Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. ;-)Regards,Mike M[/quote]Oh my... that WAS a long time ago. That was on the "BELUTION" forum which has been offline for many years... probably about 6 just as you mention. Glad to see you around!Thu, 25 Feb 2010 13:05:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHa!Well, you, a fellow named Tom(?), and I all used to try to help folks out on another SQL forum about 6 years ago. Tom was a master of Oracle, you were the SQL Server king, and I'd try to run a little cleanup with my hodgepodge knowledge of things like Access, SQL Server, and MySQL.You and I used to have friendly debates on the merits all-in-one queries versus "bite-sized" temporary sets. It's been years. I'm not *terribly* hurt that you don't remember. ;-)Regards,Mike MThu, 25 Feb 2010 12:11:26 GMTMike M - DBA2Bhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Mike M - DBA2B (2/25/2010)[/b][hr]I saw the title and author of the articles and it seemed like old times. :-)Jeff! It's been years since we last conversed, but it's good to see you're still out and about and helping the SQL community. Some things never change. Great articles!Regards,Mike M[/quote]This is terrible, Mike... I know about 6 "Mike M"s that I've not seen in several years... which one are you?Thu, 25 Feb 2010 11:20:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxI saw the title and author of the articles and it seemed like old times. :-)Jeff! It's been years since we last conversed, but it's good to see you're still out and about and helping the SQL community. Some things never change. Great articles!Regards,Mike MThu, 25 Feb 2010 09:29:26 GMTMike M - DBA2Bhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxVery cool. Glad you could use so much of the article. Thanks for the feedback, Ray.Tue, 19 May 2009 18:25:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxExcellent post, Jeff. The SQL PIVOT operator has always been a complete mystery to me, and my attempt at it today was no better. I found your solution and like it better because I can understand what I wrote without having to look up PIVOT everytime I want to read my own code!I especially liked the trick of accumulating the string value for the variable number of columns by using a SELECT instead of a loop. Very cool. :cool:You've made me a better SQL programmer today, and for that, I thank you.RayTue, 19 May 2009 16:06:27 GMTray-588024http://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThanks, Manie. I sure do appreciate the feedback.Tue, 21 Apr 2009 23:57:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Jeff Moden (4/20/2009)[/b][hr][quote][b]Manie Verster (4/18/2009)[/b][hr]Hi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work![/quote]Thanks for the great feedback, Manie.Many forums have problems with copying code and this one is no exception. There is an "easy" way to do it, though. Position your cursor just above a code window... click and drag to just below the code window to select it all. Both positions must actually be outside the code window.Then, paste to Word. Then, copy all from Word and paste to QA or SSMS. Everything except blank lines will be preserved that way... sans "gibberish".I'll be sure to include all code in a handy text file in future articles.[/quote]Jeff, thanks for the tip and it worked and thanks for a great script and article. You know, a person gets a need for pivots and crosstabs all the time and when you actually create it in the best way you know, it sometimes lack performance. A script like this is just the thing to do these things in a better way.Tue, 21 Apr 2009 23:05:16 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]Manie Verster (4/18/2009)[/b][hr]Hi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work![/quote]Thanks for the great feedback, Manie.Many forums have problems with copying code and this one is no exception. There is an "easy" way to do it, though. Position your cursor just above a code window... click and drag to just below the code window to select it all. Both positions must actually be outside the code window.Then, paste to Word. Then, copy all from Word and paste to QA or SSMS. Everything except blank lines will be preserved that way... sans "gibberish".I'll be sure to include all code in a handy text file in future articles.Mon, 20 Apr 2009 20:37:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxHi Jeff, you know sometimes you just amaze me! I love your article (got to it a bit late) but I just have one problem. I have not used this type of querying much and when I copy your code it all comes out as gibberish and I don't quite get it right to fix it so nicely. Could you possibly attach your code in a text file or something so I can download it. A .sql file will work even better. I would not like to miss this code since I get a need for this type of query all the time. Keep up the good work!Sat, 18 Apr 2009 00:16:45 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]calston (3/4/2009)[/b][hr]Jeff, this was like a God-sent article to me. It's fantastic. I do need to add a few more groupings, but without your article I would not have been able to complete this project with Dates Cross-tab dynamically. Thanks!!!Cynthia[/quote]That's outstanding news, Cynthia!! Glad to have been a help. Thanks for taking the time to post this wonderful feedback... it's very much appreciated!Wed, 04 Mar 2009 16:12:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxJeff, this was like a God-sent article to me. It's fantastic. I do need to add a few more groupings, but without your article I would not have been able to complete this project with Dates Cross-tab dynamically. Thanks!!!CynthiaWed, 04 Mar 2009 10:50:50 GMTcalstonhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxThat's some good info, Luke. Thanks for sharing it with us.Thu, 19 Feb 2009 08:30:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxJeff and J, like Jeff I'm no expert where it comes to SSRS, but I have done a decent amount of developing with them. While I'd doubt that matrixes would be faster than a pure TSQL solution, they are IMHO much easier and more flexible allowing you to drill to and reorganize your data on the fly and such. But it's the same old story, I can send 100 rows across the network and build the cross tab in SSRS or I can do it in TSQL and send 5 rows... Obviously as that scales you can see where things may start to get sticky.Additionally, I have found some instances where I just couldn't get the matrix to supply what I needed (odd totals and such) so I did it in the backend query. From what I understand most of the issues with matrices have been corrected with SSRS 2008 tablixes but I haven't had any exposure to those yet. -Luke.Thu, 19 Feb 2009 06:24:22 GMTLuke Lhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspx[quote][b]jcrawf02 (2/18/2009)[/b][hr]Jeff, I just made myself feel a whole lot better today, since I was helping someone work through a Pivot, and ended up doing much of what you demonstrate here, without realizing how much of this I had absorbed on various read-throughs, and without referencing your article. (I've read this 4 or 5 times, and only today did I actually understand it all)However, after we figured out what we were trying to do, someone said that in SSRS you can pull your dataset without pivot/crosstabbing, and then create a 'matrix report' that will display in the same manner. Do you/anyone else have experience with matrix reports? Are they truly comparable? Or is this person setting themselves up for a performance hog compared to your solution?Thanks again for your excellent writing[/quote]I have no grand experience with SSRS because I'm mostly a heavy lifter for ETL and backend batch processing code. However, I've seen enough posts to get the glimmer that SSRS matricies are quite fast (reportedly comparable to properly formed Cross-Tabs) and fairly easy to setup. I haven't seen any actual testing on them, though, and I wouldn't be surprised if pre-aggregated cross-tabs were able to edge them out by just a small margin. Just a gut feel there and I certainly could be wrong. Guess I'll have to teach myself SSRS and give it a whirl. I'd also be interested in just how "easy" it really is... PIVOTs were supposed to be easier than cross-tabs and we've all seen how that turned out. ;)By the way, thank you for the very thoughtful comments and posts... and I don't mean just on this thread. You're definitely one of the "good guys" and you've helped lots of folks. Thanks.Wed, 18 Feb 2009 22:14:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic612568-203-1.aspxJeff, I just made myself feel a whole lot better today, since I was helping someone work through a Pivot, and ended up doing much of what you demonstrate here, without realizing how much of this I had absorbed on various read-throughs, and without referencing your article. (I've read this 4 or 5 times, and only today did I actually understand it all)However, after we figured out what we were trying to do, someone said that in SSRS you can pull your dataset without pivot/crosstabbing, and then create a 'matrix report' that will display in the same manner. Do you/anyone else have experience with matrix reports? Are they truly comparable? Or is this person setting themselves up for a performance hog compared to your solution?Thanks again for your excellent writingWed, 18 Feb 2009 15:27:23 GMTjcrawf02