First few years of programming SQL; Now getting into reporting services this quarter. Very useful. THANKS!

Claudia6/28/2006 11:39:35 PM

Hi,

Brian, I’m sorry your article wasn’t published. It was probably because we covered a routine providing dynamic pivoting in the past (InstantDoc #15608, October 2000).
As dterrie guessed, timing has a lot to do with the publication of this article. The T-SQL 2005 column’s purpose is to describe new T-SQL features in SQL Server 2005 and compare them to existing ones where relevant. Since SQL Server 2005 introduced the PIVOT and UNPIVOT operators, of course I so fit to discuss it in detail and compare to the existing alternatives.

Cheers,
Itzik Ben-Gan


BG_SQL 12/18/2004 1:59:04 AM

Hey brian,
Am currently using SQL Server 2000 and exactly looking for a generic procedure which does this transposition. Do you mind sending me the stored proc code to crush_spin@yahoo.com.

Thanks in advance.

crush_spin9/28/2004 10:41:38 AM

The question this article highlights is, given the the all too common need to pivot data, how is it possible that this feature was left out of SQL Server 2000 in the first place, given that other 'lesser' MS products already have the feature? And Brian, don't take things so personally. I've been on the publishing side of the fence myself, and your article idea not being accepted was likely a matter of timing vs. merit. BTW, search for 't-sql pivot' in Google and you'll find lots of approached to the problem.

DAVID7/31/2004 10:27:09 AM

Stefano: you could definitely use my spTranspose. A call like this would let you do what you want using Ben-Gan's test data, you just have to change the WHERE clause in the @Query parameter:

exec cmv.dbo.spTranspose
@Query = 'SELECT custid, YEAR(orderdate) AS orderyear, sum(qty) as "qty" FROM dbo.Orders WHERE Year(orderdate) in (2003,2004) GROUP BY custid, Year(orderdate)',
@Group_Columns = 'custid',
@ID = 'orderyear',
@Data = 'qty'

If you want the source code, just let me know where to send it. (brian.j.parker at gmail dot com)

Of course the support for PIVOT will be the way to go in the future, but I've had to do a LOT of ad hoc pivoting where I work and will continue to need to do so until SQL Server 2005 is in our hands! I've gotten a ton of mileage from spTranspose and I'm surprised I've never seen anything like it in any of the books I've read.

Brian7/20/2004 11:32:55 AM

Not so new! It will be more interesting when I will be able to use a parametric pivot table (I mean with a parametric number of years). Thank you

Stefano Sammarco 7/2/2004 12:40:32 AM

"dynamic pivoting"-- I wonder if that's anything like an article idea I pitched about a year ago (and which you declined). Having had to do a lot of ad hoc pivots of large tables for my work, I wrote a stored procedure that will pivot anything you throw at it. So you can use a stored procedure call like this in the pubs database:

exec dbo.spTranspose
@Query = '
SELECT s.stor_id, st.stor_name, s.title_id, s.qty
FROM pubs.dbo.sales s
JOIN pubs.dbo.stores st ON st.stor_id = s.stor_id
JOIN pubs.dbo.titles t ON t.title_id = s.title_id
WHERE t.type = ''business'''
, @Group_Columns = 'stor_id, stor_name'
, @ID = 'title_id'
, @Data = 'qty'

...and get results like this:

stor_id stor_name BU1032 BU1111 BU2075 BU7832
------- ---------------------------------------- ------ ------ ------ ------
6380 Eric the Read Books 5 NULL NULL NULL
7896 Fricative Bookshop NULL NULL 35 15
8042 Bookbeat 10 25 NULL NULL

...with no additional effort. You can even send spTranspose a @ResultTable parameter and save your results into a table of your choice to further work with.

I can send source code (again), if you're interested.

brian j. parker 7/1/2004 1:35:53 PM