InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 22:58:41 GMT20http://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]mbova407 (11/16/2011)[/b][hr]I tried to create a cross tab using the normal Cross Tab method for 10k rows, the query took over 4 minutes. No where near the hundreds of milliseconds in the test results.{snip} ....I tried using MAX also.[/quote]You're also pivoting 183 columns compared to the very few from the article. That's going to make a hefty difference no matter which method you have and no matter which hardware you have.Thu, 17 Nov 2011 16:47:02 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]mbova407 (11/16/2011)[/b][hr]But 4 minutes does not justify going vertical over horizontal :-)[/quote]I'd say it does if horizontal is 10 minutes or more.Wed, 16 Nov 2011 14:12:03 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxBut 4 minutes does not justify going vertical over horizontal :-)Wed, 16 Nov 2011 13:59:45 GMTmbova407http://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]mbova407 (11/16/2011)[/b][hr]I tried to create a cross tab using the normal Cross Tab method for 10k rows, the query took over 4 minutes. No where near the hundreds of milliseconds in the test results.I tried using MAX also.[/quote]Much of the performance can come down to your hardware, that's why it is important to try both methods on your machine and see which one gives you better performance. If the other method for the same test takes 10 minutes, then 4 minutes is pretty good.Wed, 16 Nov 2011 12:13:31 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxI tried to create a cross tab using the normal Cross Tab method for 10k rows, the query took over 4 minutes. No where near the hundreds of milliseconds in the test results.[code="sql"]-- CREATE TEST DATA create table #main ( Id INT IDENTITY, [Description] VARCHAR(90))create table #attributes( Id INT, FieldName VARCHAR(128), FieldValue VARCHAR(MAX))declare @i int set @i = 1declare @c int declare @Id int WHILE @i <= 10000BEGIN INSERT INTO #main(Description) Values ('Description ' + right('00000' + convert(varchar(5), @i),5)) SET @Id = scope_identity() set @c = 1 WHILE @c <= 183 BEGIN INSERT INTO #attributes ( Id , FieldName , FieldValue ) VALUES ( @Id , 'Field' + right('000' + convert(varchar(3), @c), 3) , 'FieldValue' + right('000' + convert(varchar(3), @c), 3) + 'Description' + right('00000' + convert(varchar(5), @i), 5) ) set @c = @c + 1 END set @i = @i + 1END[/code][code="sql"]SELECT M.Id, M.Description, MIN(case when FieldName = 'Field001' then FieldValue END), MIN(case when FieldName = 'Field002' then FieldValue END), MIN(case when FieldName = 'Field003' then FieldValue END), MIN(case when FieldName = 'Field004' then FieldValue END), MIN(case when FieldName = 'Field005' then FieldValue END), MIN(case when FieldName = 'Field006' then FieldValue END), MIN(case when FieldName = 'Field007' then FieldValue END), MIN(case when FieldName = 'Field008' then FieldValue END), MIN(case when FieldName = 'Field009' then FieldValue END), MIN(case when FieldName = 'Field010' then FieldValue END), MIN(case when FieldName = 'Field011' then FieldValue END), MIN(case when FieldName = 'Field012' then FieldValue END), MIN(case when FieldName = 'Field013' then FieldValue END), MIN(case when FieldName = 'Field014' then FieldValue END), MIN(case when FieldName = 'Field015' then FieldValue END), MIN(case when FieldName = 'Field016' then FieldValue END), MIN(case when FieldName = 'Field017' then FieldValue END), MIN(case when FieldName = 'Field018' then FieldValue END), MIN(case when FieldName = 'Field019' then FieldValue END), MIN(case when FieldName = 'Field020' then FieldValue END), MIN(case when FieldName = 'Field021' then FieldValue END), MIN(case when FieldName = 'Field022' then FieldValue END), MIN(case when FieldName = 'Field023' then FieldValue END), MIN(case when FieldName = 'Field024' then FieldValue END), MIN(case when FieldName = 'Field025' then FieldValue END), MIN(case when FieldName = 'Field026' then FieldValue END), MIN(case when FieldName = 'Field027' then FieldValue END), MIN(case when FieldName = 'Field028' then FieldValue END), MIN(case when FieldName = 'Field029' then FieldValue END), MIN(case when FieldName = 'Field030' then FieldValue END), MIN(case when FieldName = 'Field031' then FieldValue END), MIN(case when FieldName = 'Field032' then FieldValue END), MIN(case when FieldName = 'Field033' then FieldValue END), MIN(case when FieldName = 'Field034' then FieldValue END), MIN(case when FieldName = 'Field035' then FieldValue END), MIN(case when FieldName = 'Field036' then FieldValue END), MIN(case when FieldName = 'Field037' then FieldValue END), MIN(case when FieldName = 'Field038' then FieldValue END), MIN(case when FieldName = 'Field039' then FieldValue END), MIN(case when FieldName = 'Field040' then FieldValue END), MIN(case when FieldName = 'Field041' then FieldValue END), MIN(case when FieldName = 'Field042' then FieldValue END), MIN(case when FieldName = 'Field043' then FieldValue END), MIN(case when FieldName = 'Field044' then FieldValue END), MIN(case when FieldName = 'Field045' then FieldValue END), MIN(case when FieldName = 'Field046' then FieldValue END), MIN(case when FieldName = 'Field047' then FieldValue END), MIN(case when FieldName = 'Field048' then FieldValue END), MIN(case when FieldName = 'Field049' then FieldValue END), MIN(case when FieldName = 'Field050' then FieldValue END), MIN(case when FieldName = 'Field051' then FieldValue END), MIN(case when FieldName = 'Field052' then FieldValue END), MIN(case when FieldName = 'Field053' then FieldValue END), MIN(case when FieldName = 'Field054' then FieldValue END), MIN(case when FieldName = 'Field055' then FieldValue END), MIN(case when FieldName = 'Field056' then FieldValue END), MIN(case when FieldName = 'Field057' then FieldValue END), MIN(case when FieldName = 'Field058' then FieldValue END), MIN(case when FieldName = 'Field059' then FieldValue END), MIN(case when FieldName = 'Field060' then FieldValue END), MIN(case when FieldName = 'Field061' then FieldValue END), MIN(case when FieldName = 'Field062' then FieldValue END), MIN(case when FieldName = 'Field063' then FieldValue END), MIN(case when FieldName = 'Field064' then FieldValue END), MIN(case when FieldName = 'Field065' then FieldValue END), MIN(case when FieldName = 'Field066' then FieldValue END), MIN(case when FieldName = 'Field067' then FieldValue END), MIN(case when FieldName = 'Field068' then FieldValue END), MIN(case when FieldName = 'Field069' then FieldValue END), MIN(case when FieldName = 'Field070' then FieldValue END), MIN(case when FieldName = 'Field071' then FieldValue END), MIN(case when FieldName = 'Field072' then FieldValue END), MIN(case when FieldName = 'Field073' then FieldValue END), MIN(case when FieldName = 'Field074' then FieldValue END), MIN(case when FieldName = 'Field075' then FieldValue END), MIN(case when FieldName = 'Field076' then FieldValue END), MIN(case when FieldName = 'Field077' then FieldValue END), MIN(case when FieldName = 'Field078' then FieldValue END), MIN(case when FieldName = 'Field079' then FieldValue END), MIN(case when FieldName = 'Field080' then FieldValue END), MIN(case when FieldName = 'Field081' then FieldValue END), MIN(case when FieldName = 'Field082' then FieldValue END), MIN(case when FieldName = 'Field083' then FieldValue END), MIN(case when FieldName = 'Field084' then FieldValue END), MIN(case when FieldName = 'Field085' then FieldValue END), MIN(case when FieldName = 'Field086' then FieldValue END), MIN(case when FieldName = 'Field087' then FieldValue END), MIN(case when FieldName = 'Field088' then FieldValue END), MIN(case when FieldName = 'Field089' then FieldValue END), MIN(case when FieldName = 'Field090' then FieldValue END), MIN(case when FieldName = 'Field091' then FieldValue END), MIN(case when FieldName = 'Field092' then FieldValue END), MIN(case when FieldName = 'Field093' then FieldValue END), MIN(case when FieldName = 'Field094' then FieldValue END), MIN(case when FieldName = 'Field095' then FieldValue END), MIN(case when FieldName = 'Field096' then FieldValue END), MIN(case when FieldName = 'Field097' then FieldValue END), MIN(case when FieldName = 'Field098' then FieldValue END), MIN(case when FieldName = 'Field099' then FieldValue END), MIN(case when FieldName = 'Field100' then FieldValue END), MIN(case when FieldName = 'Field101' then FieldValue END), MIN(case when FieldName = 'Field102' then FieldValue END), MIN(case when FieldName = 'Field103' then FieldValue END), MIN(case when FieldName = 'Field104' then FieldValue END), MIN(case when FieldName = 'Field105' then FieldValue END), MIN(case when FieldName = 'Field106' then FieldValue END), MIN(case when FieldName = 'Field107' then FieldValue END), MIN(case when FieldName = 'Field108' then FieldValue END), MIN(case when FieldName = 'Field109' then FieldValue END), MIN(case when FieldName = 'Field110' then FieldValue END), MIN(case when FieldName = 'Field111' then FieldValue END), MIN(case when FieldName = 'Field112' then FieldValue END), MIN(case when FieldName = 'Field113' then FieldValue END), MIN(case when FieldName = 'Field114' then FieldValue END), MIN(case when FieldName = 'Field115' then FieldValue END), MIN(case when FieldName = 'Field116' then FieldValue END), MIN(case when FieldName = 'Field117' then FieldValue END), MIN(case when FieldName = 'Field118' then FieldValue END), MIN(case when FieldName = 'Field119' then FieldValue END), MIN(case when FieldName = 'Field120' then FieldValue END), MIN(case when FieldName = 'Field121' then FieldValue END), MIN(case when FieldName = 'Field122' then FieldValue END), MIN(case when FieldName = 'Field123' then FieldValue END), MIN(case when FieldName = 'Field124' then FieldValue END), MIN(case when FieldName = 'Field125' then FieldValue END), MIN(case when FieldName = 'Field126' then FieldValue END), MIN(case when FieldName = 'Field127' then FieldValue END), MIN(case when FieldName = 'Field128' then FieldValue END), MIN(case when FieldName = 'Field129' then FieldValue END), MIN(case when FieldName = 'Field130' then FieldValue END), MIN(case when FieldName = 'Field131' then FieldValue END), MIN(case when FieldName = 'Field132' then FieldValue END), MIN(case when FieldName = 'Field133' then FieldValue END), MIN(case when FieldName = 'Field134' then FieldValue END), MIN(case when FieldName = 'Field135' then FieldValue END), MIN(case when FieldName = 'Field136' then FieldValue END), MIN(case when FieldName = 'Field137' then FieldValue END), MIN(case when FieldName = 'Field138' then FieldValue END), MIN(case when FieldName = 'Field139' then FieldValue END), MIN(case when FieldName = 'Field140' then FieldValue END), MIN(case when FieldName = 'Field141' then FieldValue END), MIN(case when FieldName = 'Field142' then FieldValue END), MIN(case when FieldName = 'Field143' then FieldValue END), MIN(case when FieldName = 'Field144' then FieldValue END), MIN(case when FieldName = 'Field145' then FieldValue END), MIN(case when FieldName = 'Field146' then FieldValue END), MIN(case when FieldName = 'Field147' then FieldValue END), MIN(case when FieldName = 'Field148' then FieldValue END), MIN(case when FieldName = 'Field149' then FieldValue END), MIN(case when FieldName = 'Field150' then FieldValue END), MIN(case when FieldName = 'Field151' then FieldValue END), MIN(case when FieldName = 'Field152' then FieldValue END), MIN(case when FieldName = 'Field153' then FieldValue END), MIN(case when FieldName = 'Field154' then FieldValue END), MIN(case when FieldName = 'Field155' then FieldValue END), MIN(case when FieldName = 'Field156' then FieldValue END), MIN(case when FieldName = 'Field157' then FieldValue END), MIN(case when FieldName = 'Field158' then FieldValue END), MIN(case when FieldName = 'Field159' then FieldValue END), MIN(case when FieldName = 'Field160' then FieldValue END), MIN(case when FieldName = 'Field161' then FieldValue END), MIN(case when FieldName = 'Field162' then FieldValue END), MIN(case when FieldName = 'Field163' then FieldValue END), MIN(case when FieldName = 'Field164' then FieldValue END), MIN(case when FieldName = 'Field165' then FieldValue END), MIN(case when FieldName = 'Field166' then FieldValue END), MIN(case when FieldName = 'Field167' then FieldValue END), MIN(case when FieldName = 'Field168' then FieldValue END), MIN(case when FieldName = 'Field169' then FieldValue END), MIN(case when FieldName = 'Field170' then FieldValue END), MIN(case when FieldName = 'Field171' then FieldValue END), MIN(case when FieldName = 'Field172' then FieldValue END), MIN(case when FieldName = 'Field173' then FieldValue END), MIN(case when FieldName = 'Field174' then FieldValue END), MIN(case when FieldName = 'Field175' then FieldValue END), MIN(case when FieldName = 'Field176' then FieldValue END), MIN(case when FieldName = 'Field177' then FieldValue END), MIN(case when FieldName = 'Field178' then FieldValue END), MIN(case when FieldName = 'Field179' then FieldValue END), MIN(case when FieldName = 'Field180' then FieldValue END), MIN(case when FieldName = 'Field181' then FieldValue END), MIN(case when FieldName = 'Field182' then FieldValue END), MIN(case when FieldName = 'Field183' then FieldValue END)from #main MINNER JOIN #attributes A ON M.ID = A.ID GROUP BY M.Id, m.Description[/code]I tried using MAX also.Wed, 16 Nov 2011 12:08:28 GMTmbova407http://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxVery cool. Thanks for the comparison, Stefan.Sat, 02 Oct 2010 00:08:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxJust wanted to add another data point for crasstab performance vs pivot performance.I'm creating an XML file in SSIS, there are 9 columns that are converted from rows in the table. The resulting table is around 200 meg, varying a bit day to day. The query results in around 143000 rows, again varying a bit from day to day.I ran it originally with a PIVOT to get those 9 columns and SSIS says it took 4 minutes 58.165 seconds. I ran the same process with the crosstabs and SSIS says it took 47.861 seconds.Crosstabs are over 6 times faster!Wed, 29 Sep 2010 14:30:54 GMTStefan Krzywickihttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]WayneS (9/5/2010)[/b][hr]You might be interested in the article [url=http://www.sqlservercentral.com/articles/XML/63633/][u]Using XML to Enhance the Performance of String Manipulations[/u][/url][/quote]There's one I missed... I'll check it out. Thanks, Wayne.Mon, 06 Sep 2010 10:41:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxThanks for the suggestions. The purpose of chasing the comma-delimited list of child records arose out of a practical need that's now a moot point (for now at least). The table function actually came out of playing around to see just what you could do with it.Sun, 05 Sep 2010 21:51:50 GMTdavid.c.holleyhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxDavid,I was looking at your code you posted, and I have a couple of suggestions for you.First, create the function as an in-line table-valued function (TVF). Not only is much faster this way, but you can JOIN to it.Secondly - perhaps the fastest way to create a comma-delimited string is with the FOR XML clause.So, putting both of these suggestions together, you would have:[code="sql"]CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString] ( @ShowNumber varchar(8), @ProductionDepartmentId integer)WITH SCHEMABINDING -- get just a little bit more performance out of thisRETURNS TABLE ASSELECT ShowNumber = @ShowNumber, ProductionDepartmentID = @ProductionDepartmentID, STUFF(( SELECT ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End FROM vw_ShowContacts WHERE ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId ORDER BY Id FOR XML PATH('')),1,1,'')GO[/code]The FOR XML PATH('') returns a XML like ",AC,BC,AD", etc.The STUFF function starts at the first character (the leading comma), and replaces one character with an empty string.You might be interested in the article [url=http://www.sqlservercentral.com/articles/XML/63633/][u]Using XML to Enhance the Performance of String Manipulations[/u][/url]Sun, 05 Sep 2010 20:11:34 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]Jeff Moden (9/3/2010)[/b][hr][quote]WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS :) [/quote]Thank you for the reference and for helping others, as well, Wayne.[/quote]Not a problem - I try to link to the best articles available out here, and your cross-tab/pivot articles are the best that I've seen.Sun, 05 Sep 2010 20:00:56 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]Jeff Moden (8/6/2010)[/b][hr][quote][b]david.c.holley (8/6/2010)[/b][hr]I'd consider myself an advanced beginner. I started playing around with a table valued function mostly to see what would happen. I ended up creating one that uses a function to aggregate child records for a parent into columns by category - think initials of employees by department. I then created a view that joins projects to the resultant table giving me a list of projects with staff information as in [/quote]Care to post the code, David?[/quote]1) I do not take credit for all of this as I referred to multiple articles, posts and other whatnot.2) Keep in mind, I'd call myself an advanced beginner so if may not be entirely pristine in technique, but it works.Here are the biggest elements of it. I'd have to go back and tweak things so that a full working example can be recreated. This should give you the general concepts. It breaks down into three steps1) Transform multiple child records into a single comma-delimited string using a scalar functionIn my implementation, these child records represent the initials of the staff assigned to one of 16 departments working an event.2) Use the function above to create a cross-tab using a table functionThis presents the individual departments as 16 individual columns whose value shows the initials of all staff assigned to the department for the show3) Join the result of the table function with a table for a meaningful result This function grabs the child records for a parent and converts them to a comma-delimited string. The test for Null was something unique to my implementation. (And yes, it should probably be COALESCE).[code]CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString] ( @ShowNumber varchar(8), @ProductionDepartmentId integer)RETURNS varchar(500)ASBEGIN DECLARE @names VARCHAR(500) SET @names = '' SELECT @names = @names + ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End FROM vw_ShowContacts WHERE ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId ORDER BY Id --Drop the last comma IF Len(@names) > 0 BEGIN SET @names = Right(@names,LEN(@names)-1) End RETURN @namesENDGO[/code]This table function creates a cross-tab that lists the department contacts by department for the show as in. The second value is the primary key of the department in the departments table. If we had more departments, things might get ugly, but fortunately we'll only ever have the 16.Show Depart1 Depart2 Depart4 Depart 55 AS DH, MM, TJ KL, MM18 MH JA, RV[Code]CREATE FUNCTION [dbo].[GroupContactsByDepartment_InitialsOnly]( @ShowNumber varchar(8))RETURNS TABLE ASRETURN (SELECT @ShowNumber as ShowNumber, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 1) as Depart1, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 2) as Depart2, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 3) as Depart3, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 4) as Depart4, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 5) as Depart5, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 6) as Depart6, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 7) as Depart7, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 8) as Depart8, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 9) as Depart9, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 10) as Depart10, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 11) as Depart11, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 13) as Depart12, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 14) as Depart13, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 15) as Depart14, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 17) as Depart15, dbo.getShowContactsForDepartment_AsString(@ShowNumber, 18) as Depart16)GO[/Code]Finally, this view takes the table function and joins it with the table that contains the specifics for the show turning the primary key of '5' into 'Annie' and other whatnot. I was quite surprised at how easy this was to implement once I knew the syntax. It did take some googling around to find it as the table-function examples that I found didn't demonstrate how to joing the results with another table or view.[Code]CREATE VIEW [dbo].[vw_ShowContacts_AllContactsByDepartmentInitialsOnly]ASSELECT C.* FROM ShowHeaders as SHOUTER APPLY GroupContactsByDepartment_InitialsOnly(SH.ShowNumber) AS C;GO[/Code]Sun, 05 Sep 2010 11:28:58 GMTdavid.c.holleyhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxActually, thank all you good folks. We've helped a lot of folks together over time.Sat, 04 Sep 2010 11:01:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxJeff,if I would have received 1$ each time a link to one of your articles (just to name Tally, CrossTab and DynamicCrossTab) did help an OP to solve one of their issues, I'd probably could change my job into part time... You might notice that I didn't included the QuirkyUpdate in that list. That's not because it didn't help as much as the others did. It's just to avoid starting another fire.... (but in reality it's on that list as well ;-) )Unfortunately, people tend to not posting a "thank you" note once they succeeded but almost always complain if it doesn't work as they expected (mostly due to misunderstanding or misapplying a concept).Sat, 04 Sep 2010 04:42:27 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxJeff,Just so you know, I had a similar reaction from someone asking about how to do a dynamic PIVOT via Twitter's #sqlhelp hash tag today. He/she was very impressed.I hope today is a better day for you :-)PaulFri, 03 Sep 2010 20:55:53 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote]WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS :) [/quote]Thank you for the reference and for helping others, as well, Wayne.Fri, 03 Sep 2010 20:42:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]le_billy (9/1/2010)[/b][hr]Jeff,I was having difficult time understanding how Pivot work, so I was searching the SSC forum and found thousand and thousand of posts which make my head spin(Now learn to search articles first :-P ).WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS :) ), so I took a peak, WOW!!! what a life changing moment. I like to thank you for taking the time to write such a clear and in simple detail article for us beginners.And I want to thank you for point it out that I don't need pivot for this particular report that I was writing, all the long I was cross tab myself into a corner :). Now I know where to go and improve my code.As always, you and other folks in this forum has stimulate my thinking process, challenge me to be better.Sincerely,:hehe::hehe::hehe::hehe::hehe::cool::cool::cool::cool::w00t::w00t::w00t::w00t::w00t::w00t:Billy Le[/quote]I was having a really bad day on multiple fronts today, Billy, and it got to be one of those days where it was no longer a matter of how many sticks I had in the fire but how many fires I had sticks in.Thank you for taking the time to post the wonderful feedback above. You really made my day! :-)Fri, 03 Sep 2010 20:37:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxJeff,I was having difficult time understanding how Pivot work, so I was searching the SSC forum and found thousand and thousand of posts which make my head spin(Now learn to search articles first :-P ).WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS :) ), so I took a peak, WOW!!! what a life changing moment. I like to thank you for taking the time to write such a clear and in simple detail article for us beginners.And I want to thank you for point it out that I don't need pivot for this particular report that I was writing, all the long I was cross tab myself into a corner :). Now I know where to go and improve my code.As always, you and other folks in this forum has stimulate my thinking process, challenge me to be better.Sincerely,:hehe::hehe::hehe::hehe::hehe::cool::cool::cool::cool::w00t::w00t::w00t::w00t::w00t::w00t:Billy LeWed, 01 Sep 2010 20:35:36 GMTle_billyhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]Howard C. BAchtel-438731 (8/10/2010)[/b][hr]Jeff,You responded earlier to my question by pointing me to these articles. They are very helpful and I really appreciate it.[/quote]Thanks for taking the time to write a bit of feedback, Howard. I appreciate it and I'm glad I could help. :-)Wed, 11 Aug 2010 10:53:44 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxJeff,You responded earlier to my question by pointing me to these articles. They are very helpful and I really appreciate it.Tue, 10 Aug 2010 07:31:25 GMTHoward C. BAchtel-438731http://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]le_billy (8/6/2010)[/b][hr]Thank you Jeff for your articles on Pivot and cross tabs, your example have given me some new ways to strengthen my code.Billy[/quote]Heh... you're one of the few that realize it's not all about cross tabs and pivots. Thanks for the feedback, Billy.Fri, 06 Aug 2010 12:12:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxThank you Jeff for your articles on Pivot and cross tabs, your example have given me some new ways to strengthen my code.BillyFri, 06 Aug 2010 10:57:37 GMTle_billyhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]SQL Server Youngling (8/6/2010)[/b][hr]Great article! Well worth the read, and probably future re-reads.But what are EAV's, NVP's, and CTE's? Definitions of these, or links to other articles explaining them, would improve this article. I know a number of TLA's, or three letter acronyms, but these are not among them.[/quote]Actually, I promised a 3rd cross tab article on EAVs and NVPs. I'll try to get it out sometime in the next month.Thanks for stopping by and thanks for the feedback. :-)Fri, 06 Aug 2010 10:42:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxGreat article! Well worth the read, and probably future re-reads.But what are EAV's, NVP's, and CTE's? Definitions of these, or links to other articles explaining them, would improve this article. I know a number of TLA's, or three letter acronyms, but these are not among them.Fri, 06 Aug 2010 10:21:26 GMTSQL Server Younglinghttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]Dennissinned (8/6/2010)[/b][hr]Wow. Really great article. I will never again use pivot. CROSS TAB all the way.[/quote]BWAA-HAA!!! I try not to use absolutes like the word "never" but I definitely agree in this case. Thanks for the feedback, Dennis.Fri, 06 Aug 2010 09:11:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]Tom Garth (8/6/2010)[/b][hr]Terrific article Jeff, and welcome too. I thought I was being lazy for sticking to the cross tab format for the last few years. I couldn't agree with you more regarding readability, and that important quality shouldn't be ignored.Thanks,[/quote]Heh... I don't know about you, Tom, but I [font="Arial Black"][i]AM[/i][/font] lazy... that's why I use the cross tab method. The "CPR" (Cut, Paste, Replace) method works really well in cross tabs. :-D Thanks for the feedback, Tom. I appreciate it especially on the subject of readability.Fri, 06 Aug 2010 09:09:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]david.c.holley (8/6/2010)[/b][hr]I'd consider myself an advanced beginner. I started playing around with a table valued function mostly to see what would happen. I ended up creating one that uses a function to aggregate child records for a parent into columns by category - think initials of employees by department. I then created a view that joins projects to the resultant table giving me a list of projects with staff information as in [code="plain"]Project Name DeptXEmployees DeptYEmployees DeptZEmployeesABC Liquors AH, AW, RT DH MV, EJ, BP, SS, MM, BC[/code][/quote]Care to post the code, David?Fri, 06 Aug 2010 09:06:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxWow. Really great article. I will never again use pivot. CROSS TAB all the way.Fri, 06 Aug 2010 07:43:59 GMTDennissinnedhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxTerrific article Jeff, and welcome too. I thought I was being lazy for sticking to the cross tab format for the last few years. I couldn't agree with you more regarding readability, and that important quality shouldn't be ignored.Thanks,Fri, 06 Aug 2010 07:22:52 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxI'd consider myself an advanced beginner. I started playing around with a table valued function mostly to see what would happen. I ended up creating one that uses a function to aggregate child records for a parent into columns by category - think initials of employees by department. I then created a view that joins projects to the resultant table giving me a list of projects with staff information as in Project Name DeptXEmployees DeptYEmployees DeptZEmployeesABC Liquors AH, AW, RT DH MV, EJ, BP, SS, MM, BCFri, 06 Aug 2010 07:09:54 GMTdavid.c.holleyhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxHey Joel,Not sure you saw it in my previous post because of the "edit" I did... Check out the following article. While it may not be as intelligent as Access or Excel (I agree... gotta love pivots in both of those), it does bring SQL Server a bit closer... heh... even if it was written by one of those "hard-core" developers. :-P [url]http://www.sqlservercentral.com/articles/Crosstab/65048/ [/url]Fri, 06 Aug 2010 06:37:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]joel.weiss 70857 (8/6/2010)[/b][hr]Jeff, you are totally right. Must have been a bad breakfast... my apologies. -Joel[/quote]Thanks for that, Joel. Heh... I've had coffee from a soapy cup before... you're not the only one. :-)Fri, 06 Aug 2010 06:31:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxJeff, you are totally right. Must have been a bad breakfast... my apologies. -JoelFri, 06 Aug 2010 06:29:39 GMTjoel.weiss 70857http://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]joel.weiss 70857 (8/6/2010)[/b][hr]I want dynamic pivots, i,e., pivot a table without knowing what exists in the pivot data. Nothing short will satisfy me. It seems like a religious ban on thinking by both Microsoft and the "hard-core" developers on these forums. Just do what Access and Excel both do very well, but do it in Sql Server. Is this too much to ask?[/quote]Why are you picking on 'the "hard-core" developers on these forums' and why do you believe there's a "religions ban on thinking"? Before you explain, you might try a bit of an attitude change... a lot of those supposedly limited thinkers might actually be able to help you out. ;-)You might also try doing a search to find articles about dynamic pivots...[url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]Fri, 06 Aug 2010 06:25:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxI want dynamic pivots, i,e., pivot a table without knowing what exists in the pivot data. Nothing short will satisfy me. It seems like a religious ban on thinking by both Microsoft and the "hard-core" developers on these forums. Just do what Access and Excel both do very well, but do it in Sql Server. Is this too much to ask?Fri, 06 Aug 2010 06:03:52 GMTjoel.weiss 70857http://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]roger_os (8/6/2010)[/b][hr]Thanks for the article Jeff.[/quote]You bet. Thanks for stopping by.Fri, 06 Aug 2010 05:38:12 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxThanks for the article Jeff.Fri, 06 Aug 2010 04:47:41 GMTroger_oshttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]TheSQLGuru (8/6/2010)[/b][hr]I soooo prefer the CASE syntax to PIVOT! :-) I did learn something new, which honestly doesn't happen all that often for me when it comes to the relational engine - that preaggregating the data and improve pivoting performance especially on larger datasets. Quite interesting![/quote]Thanks for the feedback, Kevin. I especially like the fact that you like the CASE syntax of a cross tab.Again, I have to give credit to fellow speed-phreak Peter "PESO" Larrson for coining the phrase "Pre-Aggregation". It's useful in places other than cross tabs, as well, but works especially well for such things.Fri, 06 Aug 2010 04:39:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspx[quote][b]Gillbert (8/6/2010)[/b][hr]Dear All, This all [HARD coded] like year or month whateverin my situations, i don't want to [HARD Coded] my pivt value, how to build the qry, if anybody's says welcome. Otherwise i will pass it to.[/quote]No problem. See Part 2 of this series...[url]http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]Fri, 06 Aug 2010 04:30:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic554742-203-1.aspxDear All, This all [HARD coded] like year or month whateverin my situations, i don't want to [HARD Coded] my pivt value, how to build the qry, if anybody's says welcome. Otherwise i will pass it to.Fri, 06 Aug 2010 04:24:05 GMTGillbert