Pivot Table Generator Code

Question

Post reply

Pivot Table Generator Code

SwePeso

SSC-Dedicated

Points: 39757
More actions
May 10, 2006 at 4:34 am

#172533

removed...
N 56°04'39.16"
E 12°55'05.25"

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

jordanac Hall of Fame Points: 3653 More actions · Answer 1

IMHO Is not pivot in sql 2005 the better option for sql 2005?

Andy.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Yes. If you know which columns you want.

No. Since you have to hardwire the column names for the PIVOT operator in SQL 2005.

With my code you don't have to.

N 56°04'39.16"
E 12°55'05.25"

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Dynamic PIVOT for 2005 (not tested)

DECLARE @tablename nvarchar(50)

DECLARE @groupby nvarchar(50)

DECLARE @pivcol nvarchar(50)

DECLARE @datacol nvarchar(50)

DECLARE @agg nvarchar(20)

SET @tablename = 'SOURCETABLE'

SET @groupby = 'ROWFIELD'

SET @pivcol = 'COLUMNFIELD'

SET @datacol = 'DATAFIELD'

SET @agg = 'SUM'

DECLARE @cols nvarchar(MAX), @sql nvarchar(MAX)

SET @cols = ''

SET @sql = 'SELECT @cols = @cols + '',['' + CAST('+@pivcol+' as nvarchar) + '']'' FROM (SELECT DISTINCT '+@pivcol+' FROM '+@tablename+') a'

EXEC sp_executesql @sql, N'@cols nvarchar(MAX) OUTPUT', @cols OUTPUT

SET @sql = N'SELECT *

FROM (SELECT '+@groupby+', '+@pivcol+', '+@datacol+',

FROM '+@tablename+') AS h

PIVOT('+@agg+'('+@datacol+') FOR '+@pivcol+' IN (' + @cols + N') AS p'

EXEC sp_executesql @sql

Far away is close at hand in the images of elsewhere.
Anon.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

That seems to work! But only for SQL 2005, sadly enough.

We still have 1 SQL 6.5, 4 SQL 7.0 and 13 SQL 2000, so this is what I come up with to make it work across all versions.

N 56°04'39.16"
E 12°55'05.25"

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Here's the technique I use more often than not when pivoting server-side: http://www.sqlteam.com/item.asp?ItemID=2955

Doing the pivot client-side can often be a good solution too.

Obviously use the technique which works best for your situation.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

regarding the sqlteam article (good).

there is a downside with using ##pivot.. ##meaning global temp tb.... global = everyone... so only one can use it at a certain time.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

steve dassin SSCrazy Points: 2396 More actions · Answer 7

If anyone would like to take a crack at reproducing the functionality of RAC bring it on

For professional crosstab development check out RAC @:

http://www.rac4sql.net

Mike Reynolds SSC Rookie Points: 46 More actions · Answer 8

As for the untested code sample. Here is a tested (and debugged) version for anyone interested;

DECLARE @tablename nvarchar(50)

DECLARE @groupby nvarchar(50)

DECLARE @pivcol nvarchar(50)

DECLARE @datacol nvarchar(50)

DECLARE @agg nvarchar(20)

SET @tablename = 'SOURCETABLE'

SET @groupby = 'ROWFIELD'

SET @pivcol = 'COLUMNFIELD'

SET @datacol = 'DATAFIELD'

SET @agg = 'SUM'

DECLARE @cols nvarchar(MAX), @sql nvarchar(MAX)

SET @cols = ''

SET @sql = 'SELECT @cols = @cols + ''['' + CAST('+@pivcol+' as nvarchar) + ''], '' FROM (SELECT DISTINCT '+@pivcol+' FROM '+@lookuptable+') a'

EXEC sp_executesql @sql, N'@cols nvarchar(MAX) OUTPUT', @cols OUTPUT

SET @cols = LEFT(@cols, LEN(RTRIM(@cols)) - 1)

SET @sql = N'SELECT * FROM (SELECT '+@groupby+', '+@pivcol+', '+@datacol+' FROM '+@tablename+') h PIVOT('+@agg+'('+@datacol+') FOR '+@pivcol+' IN (' + @cols + N')) AS p'

EXEC sp_executesql @sql

Also, a slight modification to ensure that, for example, all values in a lookup table are represented as a column;

DECLARE @tablename nvarchar(50)

DECLARE @lookuptable nvarchar(50)

DECLARE @groupby nvarchar(50)

DECLARE @pivcol nvarchar(50)

DECLARE @datacol nvarchar(50)

DECLARE @agg nvarchar(20)

SET @tablename = 'Calls'

SET @lookuptable = 'CallStatus'

SET @groupby = 'klID'

SET @pivcol = 'stID'

SET @datacol = 'clID'

SET @agg = 'COUNT'

DECLARE @cols nvarchar(MAX), @sql nvarchar(MAX)

SET @cols = ''

SET @sql = 'SELECT @cols = @cols + ''['' + CAST('+@pivcol+' as nvarchar) + ''], '' FROM (SELECT DISTINCT '+@pivcol+' FROM '+@lookuptable+') a'

EXEC sp_executesql @sql, N'@cols nvarchar(MAX) OUTPUT', @cols OUTPUT

SET @cols = LEFT(@cols, LEN(RTRIM(@cols)) - 1)

SET @sql = N'SELECT * FROM (SELECT '+@groupby+', '+@pivcol+', '+@datacol+' FROM '+@tablename+') h PIVOT('+@agg+'('+@datacol+') FOR '+@pivcol+' IN (' + @cols + N')) AS p'

EXEC sp_executesql @sql

As for Peter Larsson ("That seems to work! But only for SQL 2005, sadly enough."). He forgot to mention that there were a few errors in the code sample which, if he got it to work, he must have found and solved. Please Peter, why not just post the revised code in this case so that we all do not have reinvent the wheel time and time again!???

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

This works for all versions.

http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

N 56°04'39.16"
E 12°55'05.25"