Getting Schooled on Dynamic Pivot… Or, PIVOT Part 2

A note: I’m reposting this because I accidentally deleted it from WordPress. Because I’m an idiot.

I wrote a post about Overcoming my Fear of Pivot. With my newfound confidence, I decided to tackle dynamic pivots. This is a common scenario where you need to PIVOT, but you don’t know exactly what you’re going to end up with. Basically, you want to allow all of the possible column headers to come back with the aggregated data you need.

If you’re not familiar with PIVOT, go back and read the original post. If I’ve done my job properly, it should make sense. So, here’s what I did… I resisted the urge to hit Google to find a solution to the dynamic pivot problem. I opened SSMS and said, “Self, you’re under a deadline. Write it and see if you can get it to work all by your lonesome”. 45 minutes later, I had a working script that produced some cool real-world output, if I do say so myself.

Then, I hit Google. Then I saw Itzik Ben-Gan’s solution. My first response was, “Crap!” Actually, it was a much less ladylike expletive than that. The solution was… Beautiful. Elegant. Blew my method out of the water. You know how athletes have muscle memory? Well, developers have it too. We fall back to what’s comfortable and familiar. Sort of like our own version of T-SQL sweatpants and chocolate ice cream. Before I start in on the comparison of my solution and Itzik’s, let me say this: His is so much better than mine. Did I mention that it was elegant? And beautiful? But you know what? In a real development environment, with deadlines and giant to-do lists, I would have fallen back to my own comfort zone. I know this. I also know that next time I need to write a dynamic PIVOT, I’m going to know how to use his method.

Authors, when asked to give advice to aspiring writers, always say the same thing. “Write what you know.” For us IT Folk, there’s a corollary. “Write what you know. Hit the deadline. Then, go learn a better way.” Am I proud that I figured a solution out on my own? Yup. Am I a bit deflated that I didn’t come up with the same solution as Itzik Ben-Gan? Nope. Come on, it’s Itzik.

Personal note: I hate when I run across someone else’s T-SQL and ask them, “How does this work?”, and their response is, “I don’t know, I found it on a blog post/Google/forum.” Peeps, this is unacceptable. Don’t copy and paste until you understand what you’re seeing. Because someday you’re going to have to maintain that pilfered bit of code. If you don’t know what it does, then don’t use it. Comprehend your own code. We all borrow from the experts, but make sure you can explain it in 50 words or less. If you can’t, then back away from the Ctrl+V. Stretch your skills, learn new things, just don’t jeopardize a project by jumping the gun.

Okay, enough commentary. On to the solutions. The trick in a dyamic PIVOT is to create a string that has all of the column headers you need. This is where he and I diverged wildly. I fell back on a WHILE Loop over a set of rows contained in a table variable, he used the STUFF function with a FOR XML Path() query output. I wrote my solution to address the same example from BOL that I ranted about in my first post. I modified his solution to produce the same output, and to clean out some unused variables that were in the sample I found. I’ve also resisted the urge to make little tweaks to my script after doing some extra research. Truly, I want to make the point that there’s what works… and what works beautifully.

My solution:

SET NOCOUNT ON;

DECLARE @vEmployeeIDTable as TABLE
(
EmployeeID varchar(20) NOT NULL
,ProcessedFlag bit NOT NULL DEFAULT(0)
)

DECLARE @vEmployeeID varchar(20)
DECLARE @vSQLString varchar(max) = ”
DECLARE @vEmployeeIDSELECT varchar(max) = ”
DECLARE @vEmployeeIDFOR varchar(max) = ”
DECLARE @vLoopCounter varchar(50) = 1

INSERT INTO @vEmployeeIDTable(EmployeeID)
SELECT DISTINCT EmployeeID
FROM Purchasing.PurchaseOrderHeader;

WHILE (SELECT count(ProcessedFlag) FROM @vEmployeeIDTable WHERE ProcessedFlag = 0) > 0
BEGIN

SELECT @vEmployeeID = ‘[‘+cast(MIN(EmployeeID) as varchar(20)) +’]’
FROM @vEmployeeIDTable
WHERE ProcessedFlag = 0

SET @vEmployeeIDSELECT = @vEmployeeIDSELECT + @vEmployeeID + ‘ as Emp’+@vLoopCounter+’,’
SET @vEmployeeIDFOR = @vEmployeeIDFOR + @vEmployeeID +’,’

UPDATE @vEmployeeIDTable
SET ProcessedFlag = 1
WHERE EmployeeID = cast(substring(@vEmployeeID,2, LEN(@vEmployeeID)-2) as int)

SET @vLoopCounter = @vLoopCounter + 1

END

SET @vEmployeeIDSELECT = SUBSTRING(@vEmployeeIDSELECT,1, len(@vEmployeeIDSELECT)-1)
SET @vEmployeeIDFOR = SUBSTRING(@vEmployeeIDFOR,1, len(@vEmployeeIDFOR)-1)

SET @vSQLString = ‘
SELECT VendorID, ‘+@vEmployeeIDSELECT +’
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
(‘+@vEmployeeIDFOR+’)
) AS pvt
ORDER BY pvt.VendorID; ‘

PRINT @vSQLString

EXECUTE (@vSQLString)

So, a quick rundown of what I did:

1) Create a table variable (@vEmployeeIDTable). Populate it with DISTINCT EmployeeID’s from Purchasing.PurchaseOrderHeader.
2) Declare the following variables:
a) @vEmployeeID – holds the EmployeeID I’m concatenating into the string during the WHILE loop
b) @vEmployeeIDSELECT – holds the EmployeeID string that I’ll use in the SELECT clause of my PIVOT. I separate this one out because I want to concatenate the column aliases just as they were in the BOL example.
c) @vEmployeeIDFOR – holds the EmployeeID string that I use in the FOR clause of my PIVOT. I don’t need column aliases here.
d) @vLoopCounter – holds a counter as I loop through the string concatenation. I use it to help name my column aliases (Emp1, Emp2…). The 1 and 2 are coming from this variable
3) While I have unprocessed rows in my table variable, I loop through with a WHILE
a) Set @vEmployeeID to the minimum EmployeeID that hasn’t been processed. I also concatenate on the brackets I need since these will become column names. (Those brackets were a pain. I kept having to work around them. Another place where Ben-Gan’s method was more elegant)
b) Set @vEmployeeIDSELECT to itself plus the EmployeeID being processed (@vEmployeeID), and then set up the alias. (as ‘Emp’+@vLoopCounter). Important note: I initialized the variable as an empty string (”). This is so that I’m not trying concatenate a NULL value to a string on the first go-round.
c) Set @vEmployeeIDFor to itself plus the EmployeeID being processed
d) Update @vEmployeeIDTable to indicate that the EmployeeID has been added to the string variables
e) Update @vLoopCounter so that the next table alias will be the next number
4) Clean up the extra commas at the end of the string variables
5) Put the whole thing together in @vSQLString
a) Place the @vEmployeeIDSELECT variable where it needs to go
b) Place the @vEmployeeIDFOR variable where it needs to go
6) Execute the variable @vSQLString

This is the output:


Okay, not bad. Now, the elegant Itzik Ben-Gan solution:

DECLARE
@cols AS NVARCHAR(MAX),
@sql AS NVARCHAR(MAX);

SET @cols = STUFF(
(SELECT N’,’ + QUOTENAME(EmployeeID) AS [text()]
FROM (SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader) AS Y
ORDER BY EmployeeID
FOR XML PATH(”)),
1, 1, N”);

SET @sql = N’SELECT ‘+@cols +’
FROM (SELECT VendorID, EmployeeID, PurchaseOrderID
FROM Purchasing.PurchaseOrderHeader) AS D
PIVOT(COUNT(PurchaseOrderID)
FOR EmployeeID IN(‘ + @cols + N’)) AS P
ORDER BY P.VendorID;’;

PRINT @SQL

EXEC sp_executesql @sql;
GO

I know, right? Elegant. So what did he do?

1) Declared a couple of variables
a) @cols – holds the string of column values for the PIVOT
b) @sql – holds the SQL statment that gets executed
2) Used a FOR XML PATH(”) command to concatenate the string. This is cool. The query pulls EmployeeID’s out of a derived table in the FROM Clause. He orders by EmployeeID (which is not required), and outputs the result of this query using FOR XML PATH(”). The FOR XML PATH(”) clause creates a single row that looks like this:

,[250],[251],[252],[253],[254],[255],[256],[257],[258],[259],[260],[261]

Wow, exactly what we need for the PIVOT. Well, almost. That’s what the STUFF function is for. Getting rid of “almost”.

3) Also, see how he used QUOTENAME to add the brackets he needed?

QUOTENAME(EmployeeID) AS [text()]

4) Then, since that leading comma (,[250]) is not needed, he uses the STUFF command to strip it off. STUFF looks like this:

STUFF ( character_expression , start , length ,character_expression )

a) character_expression – the results of the query containing the FOR XML PATH(”) output
b) start – first character
c) length – how many characters to replace with what we’re “stuffing” in. In this case, a length of 1.
d) character_expression – an empty string, which is what’s’ “stuffed” into the first character expression, eliminating the comma.

Try this to illustrate it much more simply:

SELECT STUFF(‘abcdef’, 1, 1, ”);

Your result is: ‘bcdef’. The empty string he specified basically replaces the first character which is the comma we don’t want. Seriously, I had to run the baby STUFF to understand it properly. The beauty of STUFF over SUBSTRING is that SUBSTRING requires you to tell the function the length of the resulting string, which would require a LEN function over the entire subquery to get it right. It saves you having to execute that bad boy more than once.

5) Finally, he just puts the PIVOT query into @sql, concatenating in @cols where he needs to, and then executes it.

This is his output:

So he didn’t do pretty column aliases, but the important data is the same. And just take a look at the execution plans. That’s where I do feel just a bit deflated. Mine is monstrous. His? TWO queries. TWO! But that’s not the point. The point is, I had a blast figuring out how to write my own dynamic PIVOT. I had even more fun dissecting Itzik Ben-Gan’s method. (Yeah, I know. I’m a dork.) And, you can bet your sweet bippy that I’ll be working to make sure that FOR XML PATH, STUFF, and QUOTENAME all become part of my T-SQL muscle memory.

On Overcoming My Fear of PIVOT

I’m intimidated by PIVOT.  I’ve had a heck of a time wrapping my head around it, which is shameful, because Junior Accountants have been making pivot charts in Excel for years.  They get it, so why can’t I?  Well, I’ve got a few theories, mostly related to my occasional fear of unfamiliar things, and of feeling dumb.  Anyway, I finally got into a situation where I couldn’t avoid it, and I had to dig in there and learn it.  Nothing like a deadline to make you act like a proper student. 

I went to BOL, and looked it up.  Now, I’m a fan of Books Online.  It saves my tush daily.  But in this case… I’m sorry, but the explanation is nonsensical.  I mean, I read it, and what I comprehend is, “blah, blah, PIVOT, blah, you’re an idiot, Audrey, just give up now”. 

So, being forced to use a PIVOT, I had to break it down into chunks that my tiny brain could consume.  So, first, let’s look at the BOL syntax: 

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    …

    [last pivoted column] AS <column name>

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    … [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;
Hoo-kay.  I’m going to step you through my process of understanding this so I could construct my own PIVOT.  I’m even going to use the complex pivot example from BOL, the AdventureWorks2008 database.  We’re going in this order:  FROM, PIVOT, FOR, SELECT. 

But first, some rules.  There are always rules: 

RULES: 
1) You have to know how many columns you’re going to end up with after the PIVOT.  This means that this operation is great for things like months in a year, not so great for a varying number of pivoted columns.  You can tell it which columns to return, but the bottom line is you need to know what your output should look like.  If you want to break this rule, you’re writing dynamic SQL. 
2) You’re going to have to aggregate.  Even if you don’t really want to.  It’s required, but as always, there are ways to work the syntax.

THE BOL QUERY EXAMPLE: 

SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

THE BOL QUERY OUTPUT: 

 

THE BREAKDOWN: 

1) FROM (Source Query):  This is the derived table that lives in the FROM clause.  It produces the data that is going to be aggregated and pivoted.  Write this first.  Get familiar with what data you’re working with.  Don’t forget to give it an alias.  I like the ever-creative “as SourceQuery” to help me remember what that derived table’s doing there in the first place. 

FROM

    (<SELECT query that produces the data>)

    AS <alias for the source query>
   
In the BOL example, this is the Source Query: 

FROM (
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) as p

It returns this: 

This is our raw data.  By the time we get to the bottom of this blog post, we’re going to COUNT PurchaseOrderID’s by EmployeeID, set some EmployeeID’s as column headers, and return what looks like a cross-tab report with VendorID’s as row headers, EmployeeID’s as column headers, and PurchaseOrder COUNT as detail data.  Really.  I promise. 

2) PIVOT (Aggregation/Summarization):  This is where you’re saying how to aggregate, or summarize what will end up in the cells.  Think of it this way:  If this were a spreadsheet, with column headers and row headers, the data produced by the PIVOT clause is the detail data living in the cells.  Now, you don’t always want to aggregate.  Sometimes you don’t have anything to aggregate, you just want to flip your data from rows to columns.  Too bad.  You’re aggregating something.  The solution I’ve seen is to do a MIN or MAX, but to make sure that the MIN or MAX is of a unique thing.  You’ll have to examine your data to see what works for you.  But back to PIVOT…

PIVOT
(
<aggregation function>(<column being aggregated>)

In the BOL example, it looks like this: 

PIVOT
(
COUNT (PurchaseOrderID)

So, what it’s saying is that the “detail” data (think like you’re in Excel for a moment) should be the count of PurchaseOrderID’s.  Simple enough.  But where’s my GROUP BY?  It feels like heresy, aggregating something without a GROUP BY.  Hang in there…

3) FOR (Sort-of GROUP BY):  FOR establishes what will be column headers for the PIVOT-ed (aggregated) data.  One cool thing about it not being a true GROUP BY is that I don’t have to include everything from my Source Query (FROM).  If you look at the BOL example, VendorID from my Source Query (FROM) isn’t included in the PIVOT or FOR clauses.  It’s a pass-through column.  It’s going to be there in the SELECT, and therefore in the output, but it isn’t part of the PIVOT process.  In fact, you don’t have to include VendorID at all.  The data probably wouldn’t make sense, but to each his own, right? 

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    … [last pivoted column])

) AS <alias for the pivot table>

In the BOL example, the query developer chooses to return the number of purchase orders for a specific set of Employees.  Yes, in the example it’s arbitrary, because they return 5 and there are actually 12 distinct EmployeeID’s in the Purchasing.PurchaseOrderHeader table, but I’m not here to judge.  How do they do this?  Like this: 

FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt

This is telling the PIVOT to produce 5 columns, [250], [251], [256], [257], and [260].  (You don’t have to have the brackets, except that “250” wouldn’t be a valid column name without them.)  Those numbers are the actual EmployeeID’s returned from the Source Query.  You’re saying “FOR” an EmployeeID “IN” a specific set of values that were returned in the Source Query (FROM).  You’re essentially establishing a GROUP BY on EmployeeID.  What’s being “grouped” by the FOR clause?  The data that you’re aggregating in the PIVOT clause.  Cool, huh?  The COUNT of PurchaseOrderID’s will be placed underneath the column corresponding to the EmployeeID it belongs to.  Don’t forget to alias the FOR clause.  Something like “IRockBecauseIFiguredThisOut” works well.  🙂 Also, this is where you’re going to close the parenthesis that you opened up in the FROM clause. 

Personal Note:  This clause is one of the reasons I hate this BOL example.  It doesn’t make sense that I would hard-code EmployeeID’s.  A PIVOT example with months or years or something would be a more likely real-world scenario.  Making it an example implies that it’s a good idea, and that every person reading BOL knows not to assume that Employee 257 will be a lifer at Adventure Works.  But like I said, I don’t judge. 

4) SELECT (Presentation):  Why is it that SELECT is always the simplest part of a query?  It seems so important, but it really doesn’t do much.  It’s like the presentation layer of the query.  Here, you’re telling the query what to output.  As long as it was part of the Source Query (FROM), or defined as a column header in the FOR clause, you can include it in the SELECT clause.  In fact, if you’re feeling frisky, you can leave off columns.  The query doesn’t care, because the SELECT is just there to make things pretty. 

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,

    …

    [last pivoted column] AS <column name>

In the BOL example, it looks like this: 

SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5

VendorID is a pass-through (non-pivoted) column.  It’s there to supplement the PIVOTed data.  The other columns are the ones we established in the FOR clause.  Just remember that everything you want to work with needs to be included in that Source Query (FROM clause). 

Putting it all together, it looks like this: 

SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

The output looks like this: 

 

So there you have it.  A peek into my thought process as I worked to overcome my fear of PIVOT.  I’m good now.  I’ll still have to look up the syntax whenever I write it, but at least I won’t break out in to a cold sweat next time.  And next up for me… PIVOT with an unknown/dynamic number of output columns.  Woo-hoo!  Dynamic SQL! 

Query on, my friends.



On Bobby Cox

Today is Day 1 A.B. (After Bobby)  I can’t help tearing up a little when I watch the video of his last post-game press conference.  He was a class act and I can’t imagine baseball, especially Braves baseball, without him. 

I know what you’re thinking… “Datachix, what does this have to do with data?”  Well, Dear Reader, let me tell you.  We could all learn a thing or two from the legendary Skipper.  Actually, there are five. 

1) Loyalty is NOT overrated.  I don’t know that I’ve ever heard Bobby say a harsh word about his players, even after a loss.  He might point out mistakes they’d made, or reasons why they lost, but he immediately followed it up with a comment about how they’d get them next time, and how his boys played hard.  I don’t know about you, but I wouldn’t have been able to resist bashing Brooks Conrad after 3 errors cost us Game 3 in the NLDS.  What did Bobby say about whether he’d bench him?  “I’ll have to sleep on it.”  That’s classy.  How many times have I railed against a fellow developer who broke a build?  How many times have I complained loudly about how a project was going?  More times than I’d care to admit.  And, managers all over IT could use his playbook to improve their management style.  In a perfect world, Bobby would become the development manager on my next project.  Tell me you wouldn’t work harder for him than anyone. 

2) Stand up for your team.  He’s been ejected over 150 times (161 I believe, if you count the post-season).  If Bobby saw an injustice being carried out by the umpires, he was going to get out there and in someone’s face.  Now, I don’t suggest that we all get ourselves carted out of the building by security next time something unfair happens, but there is something to be said for a manager who’s willing to step in and take the heat for his guys.  Many times, he would step in and get himself ejected to save one of his players.  Next time you see a co-worker getting treated unfairly, ask yourself:  What Would Bobby Do? 

3) Find your passion and live it.  Bobby Cox started playing baseball professionally over 50 years ago.  Can you even imagine spending 50 years of your life doing anything?  If you truly love what you do, then good for you.  If not, then find it.  If nothing else, find something you love about your career and focus on that.  Bobby was a mediocre baseball player.  He only really played two seasons professionally.  How lucky are we that he decided to stick with the game that he loved and find a way to be a part of it instead of going off to sell insurance somewhere? 

4) Be a mentor.  Ask anyone who’s played for Bobby.  He’ll tell you that Bobby was a mentor, a teacher.  Jason Heyward will be a better player because he got just one season with Bobby.  There’s a reason that the Braves clubhouse is considered one of the most professional and welcoming in Major League Baseball.  Bobby set the bar, and then taught his guys how to reach it.  HOw many less experienced people do you work with every day?  Do you shrug and roll your eyes when they struggle, or do you step in and patiently teach them how to improve.  Again, What Would Bobby Do? 

5) Talk softly, but don’t be a softie.  Okay, so Bobby’s temper was legendary (note the 161 ejections).  But for the most part, he’d sit there on the dugout bench or lean against the railing, quietly watching the game unfold.  He didn’t rant.  He didn’t rave.  He made decisions, sometimes tough ones, with respect and thoughtfulness.  I see so many of us (myself included), so eager to prove our worth and expertise bluster and proclaim our ideas for the world to hear.  How about we take a page from Bobby’s book and just let the record speak for us?  How about we make tough decisions with less drama?  How about we sit back and watch the game unfold for a while before we jump to conclusions?  That’s Bobby’s way. 

Anyone who knows me personally knows that I love my Braves.  When I adopted Atlanta as my second hometown, I was lucky to have a great baseball team to go along with it.  Bobby Cox was a big part of that.  In an age of juiced players, and scandals, and waning interest in the sport, Bobby represented an old school approach to a game that I’d grown up with.  So, if you ever see me sitting back, quietly watching the game unfold, resisting the urge to jump in with opinions and half-formed ideas, you’ll know I’m getting my Bobby on.

And from @Datachix1 ‘s Mom…. #SqlSat48

Like I mentioned in my post, my mom (Karen Smith, not on the Twitter yet)  came with me to Columbia and decided to come to the day’s events.  She attended sessions all day, sometimes with me, sometimes on her own.  She branched out on her own for two sessions–David Taylor’s “To Click or Not to Click” and Jose Chinchilla’s “Get Cert! Get Cred!”  here are some of mom’s thoughts:

What I most appreciated about David Taylor’s ( @DYFHID ) presentation was his infectious enthusiam for all things SQL.  In addition he offered very practical advice for anyone like him who becomes an accidental DBA. Very good advice for beginners like me.
 
Jose Chinchilla has a plan to get certified: ( @sqljoe ) the why, where and how.  Lots of money saving tips and advice. I came away with something to aspire to! 
 
 
As for SQL Saturdays and PASS, I can’t say enough about them. I’ve worked at conferences and attended a few. I have to say this gang really know how to put on a seminar:  great sessions, great topics, great speakers, really nice venue and food! The organizers really know how to pull it off. This is a great community!

#sqlsat48 Datachix1’s Wrapup. Columbia SC/October 2, 2010

I had a great time at this past Saturday’s event in Columbia, SC where I  was lucky enough to be presenting.  I forgot to mention during my presentation –“Cool Tricks to Pull from your SSIS Hat” –that Carolina is my alma mater, and the Columbia area was home to me and my parents for 8 years. 

This was my first time back in 15, and besides the great professional event, I got to see several dear dear, friends from my previous life which was in Theatre.  I stayed at the lovely Hampton Inn in downtown Columbia (in the newish Vista area).  My mom came with me and we caught Rent at Trustus Theatre, which was only a few blocks from the hotel.  What an amazing show!  Shout out to the folks at Trustus for 26 years of theatrical awesomesauce. 

My mother happens to be a smart lady who is also interested in the SQL Server lifestyle, so she also came with me to the speaker’s dinner and the event itself.  It was really nice to share a little of my career with her.  I’m pretty sure she loved it.

This event was swarming with MVP’s and VIP’s and I got a chance to meet quite a few great folks and cavort with people I already knew. 

Where should I start?  Maybe with my some of my homies from Atlanta…..

Mark Tabladillo.  He’s a super smart guy with whom I shared a great conversation at the Speaker’s Dinner about Data Mining and ways to test the trustworthiness of your results and predictions.  My conversation with Mark illustrated to me the beauty of SQL Saturdays and events like them—you can not only learn cool and useful things, but enjoy great conversations and build relationships from which you can continue learning. 

Bob Langley.  Bob sat next to me at the speaker’s dinner and I was pretty sad to learn that we were scheduled to speak in the same time slot, because his presentation on Column Level Encryption really interested me.  This is a request I have received before, and I want to see what the possibilities are.  This was Bob’s first speaking engagement—kudos for speaking.

Stuart Ainsworth.  Stu did 3, yes 3 presentations.  I was not able to make any of them because of the restraining order, (just kidding), but I did hear that he did a great job.  He reported that his lunchtime demo was slightly hindered by a runaway process which ate his laptop, but I don’t think anyone noticed because

— the LUNCH was AWESOME!

                And FREE!  Thanks Sponsors

Special thanks also to Brian Kelley and Bobby Dimmick (and all the volunteers, especially Paul Waters who was my AV helper and the lovely man who watched our stuff all day in the speaker’s room).  The day really did go beautifully. 

Now the Outliers:

David Taylor from Alabama by way of Boston.   David was doing a new presentation on DBA tasks which my mother loved.  David is a recent convert to SQL Server whose enthusiasm for our community is palpable.  I always enjoy seeing David.

Jose Chinchilla.  Jose is a super nice guy that  I got to sit with at the Speakers Dinner as well.  He is running a SQL Saturday event in Tampa in January of 2011, which I hope to make.  Mom sat in on his certifications session, and she is really excited about getting some certifications now. 

Andy Leonard. (Blog | Linkedin | Twitter)  The Man . The Myth. The Moustache  :{>.  Andy is one of those Rock Star names in BI that I was super excited to get a chance to meet and hear present and he didn’t disappoint.  He loves his work and he loves his family and he loves sharing the tools of our trade with people.  I watched both of his presentations and got to learn how bigger shops can standardize large deployments and solutions, with clever safeguards in place.  He took time to talk with me about my presentation, sharing with me some additional xml tools I could use and giving me praise which made me all giggly.

Andy Warren. (Blog | Linkedin | Twitter) Co-founder of SQL Saturday, SQLServerCentral and a Director at Large of PASS.  Andy’s presentation on developing yourself professionally was well received and given that bio, I think he knows of what he speaks.  He encouraged us to think of ourselves as a product or business, and to develop a business plan accordingly.  I concur. 

Jessica Moss.  (Blog | Linkedin | Twitter) So glad I got to meet Jessica and see her presentation on Reporting Services.  Jessica is a great speaker—people were engaged and learning and she has a great time up there, which I love to see.

My presentation went very well.  I had 20 folks and I felt like most of my group was really enjoying it.  Several folks said they learned at least one or two very useful things.  I realized when it was over that I didn’t get very many questions, but people were definitely engaged, so I hope I wasn’t bulldozing them.  Got an average score of 4.6 (out of 5)—the large majority of people liked the presentation a lot. 

My advice to first time speakers:  (Robert Cain also gave me this advice):  practice with 1024×768 resolution.  You would be amazed how much the low resolution will throw you off, and how frequently you encounter it.  Also, practice your presentation with people in an audience—I inflict it on my coworkers.  I did practice runs on Tuesday and Thursday.  Tuesday really kind of sucked (thanks dear coworkers for suffering through it), on Thursday I felt much better and by Saturday I was pretty happy.