Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, September 01, 2010 8:35 PM
 Grasshopper Group: General Forum Members Last Login: Sunday, November 03, 2013 12:58 PM Points: 21, Visits: 235
 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 ).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,Billy Le
Post #979250
 Posted Friday, September 03, 2010 8:37 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 5:55 PM Points: 34,565, Visits: 28,749
Post #980570
 Posted Friday, September 03, 2010 8:42 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 5:55 PM Points: 34,565, Visits: 28,749
 WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS :) Thank you for the reference and for helping others, as well, Wayne. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #980571
 Posted Friday, September 03, 2010 8:55 PM
 SSChampion Group: General Forum Members Last Login: Saturday, December 07, 2013 7:09 PM Points: 11,052, Visits: 10,818
 Jeff,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 Paul Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #980572
 Posted Saturday, September 04, 2010 4:42 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 3:25 AM Points: 6,870, Visits: 12,511
 Jeff,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). LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #980614
 Posted Saturday, September 04, 2010 11:01 AM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 5:55 PM Points: 34,565, Visits: 28,749
 Actually, thank all you good folks. We've helped a lot of folks together over time. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." "Change is inevitable. Change for the better is not." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #980652
 Posted Sunday, September 05, 2010 11:28 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, September 20, 2010 6:12 AM Points: 105, Visits: 172
 Jeff Moden (8/6/2010)david.c.holley (8/6/2010)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 Care to post the code, David?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).`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`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`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`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.`CREATE VIEW [dbo].[vw_ShowContacts_AllContactsByDepartmentInitialsOnly]ASSELECT C.* FROM ShowHeaders as SHOUTER APPLY GroupContactsByDepartment_InitialsOnly(SH.ShowNumber) AS C;GO`
Post #980806
 Posted Sunday, September 05, 2010 8:00 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 6:45 PM Points: 6,464, Visits: 8,545
 Jeff Moden (9/3/2010)WayneS has a link to your Cross Tabs and Pivots article on his signature line(thank you WayneS :) Thank you for the reference and for helping others, as well, Wayne.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. WayneMicrosoft Certified Master: SQL Server 2008If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #980878
 Posted Sunday, September 05, 2010 8:11 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 6:45 PM Points: 6,464, Visits: 8,545
 David,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:`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`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 Using XML to Enhance the Performance of String Manipulations WayneMicrosoft Certified Master: SQL Server 2008If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #980881
 Posted Sunday, September 05, 2010 9:51 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, September 20, 2010 6:12 AM Points: 105, Visits: 172
 Thanks 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.
Post #980895

 Permissions