XTAB - SIMPLE CROSSTAB UTILITY
-
sp_XTAB (see Script library)
------------------------------------- example
Use Northwind
EXEC sp_XTAB
'Northwind',
'xtORDIST',
'Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID',
'Orders.CustomerID, City',
'ShipCountry + ''_'' + CAST(Year(ShippedDate) AS varchar(4))',
'WHERE (Year(ShippedDate) <> NULL)',
'OrderID',
'COUNT',
'NULL',
'WHERE Orders.CustomerID Not Like ''W%''',
'SELECT xtORDIST.* FROM xtORDIST DROP VIEW xtORDIST'
-- The two WHERE clauses and the last parameter are OPTIONAL.
-- enter optional parameters as '', when not required.
-- NULL goes with COUNT/Characters and 0 (zero) with SUM/numerics.
-
This looks very interesting. Can we get it uploaded to the Scripts library rather than trying to cut/paste from the Forum?
-
Hi Ian,
You know, after writing a mountain of code like that, I don't think you can consider yourself a newbie to SQL Server anymore. I'm glad you posted this!
Chris
-
Thanks Chris,
I'm a programmer who knows very little about SQL Server.
This is a prototype and sql gurus such as yourself could improve XTAB I.e. more error trapping.
The "scoping" is a bit tricky to work with I.e. the treble nesting of dynamic EXEC statements.
It's now in the Scripts Library.
Ian
-
...I'm a programmer who knows very little about SQL Server ...
... but I bet that's changing fast!
...sql gurus such as yourself could improve XTAB ...
Thanks, but I believe my official title is Helpful Hack...
-
XTAB is now in the Scripts library and I was really chuffed to receive so many votes.
Ian
Use Northwind
EXEC sp_XTAB
'Northwind',
'xtORDIST',
'Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID',
'Orders.CustomerID, City',
'ShipCountry + ''_'' + CAST(Year(ShippedDate) AS varchar(4))',
'WHERE (Year(ShippedDate) <> NULL)',
'OrderID',
'COUNT',
'NULL',
'WHERE Orders.CustomerID Not Like ''W%''',
'SELECT xtORDIST.* FROM xtORDIST DROP VIEW xtORDIST'
-
Chuffed? Is that like "cheerfully fluffed?"
-
Yeah, something like that.
It's an expression which (giving away my age) comes from the 1950s.
Ah, the days of Rock 'n Roll when Elvis became the KING and life was about "playing the game" rather than "winner takes all".
Favorite quote: "We do not inherit the land, we borrow it from our children."
Breaking news: South Kilworth (my village) has 101 houses and British Telecom say we can have broadband, IF we collect 200 signatures. Nice one BT.
Ian
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply