Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2021222324»»

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 8:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 3, 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 3, 2010 8:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
le_billy (9/1/2010)
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


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!


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980570
Posted Friday, September 3, 2010 8:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980571
Posted Friday, September 3, 2010 8:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:58 AM
Points: 11,192, Visits: 11,097
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 White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #980572
Posted Saturday, September 4, 2010 4:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 7,040, Visits: 12,966
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).




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #980614
Posted Saturday, September 4, 2010 11:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #980652
Posted Sunday, September 5, 2010 11:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 steps
1) Transform multiple child records into a single comma-delimited string using a scalar function
In 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 function
This presents the individual departments as 16 individual columns whose value shows the initials of all staff assigned to the department for the show

3) 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)
AS
BEGIN

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 @names

END


GO

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 5
5 AS DH, MM, TJ KL, MM
18 MH JA, RV

CREATE FUNCTION [dbo].[GroupContactsByDepartment_InitialsOnly]
(
@ShowNumber varchar(8)
)
RETURNS TABLE
AS
RETURN
(
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]
AS
SELECT
C.*
FROM
ShowHeaders as SH
OUTER APPLY GroupContactsByDepartment_InitialsOnly(SH.ShowNumber) AS C;


GO

Post #980806
Posted Sunday, September 5, 2010 8:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 6,582, Visits: 8,861
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.


Wayne
Microsoft Certified Master: SQL Server 2008
If 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 5, 2010 8:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 6,582, Visits: 8,861
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 this
RETURNS TABLE
AS
SELECT 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


Wayne
Microsoft Certified Master: SQL Server 2008
If 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 5, 2010 9:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
« Prev Topic | Next Topic »

Add to briefcase «««2021222324»»

Permissions Expand / Collapse